Get-SqlServerPermission returns error

Apr 26, 2012 at 2:27 AM

get-sqlserverpermission 'xxxx'
Exception calling "EnumServerPermissions" with "0" argument(s): "Index was out of range. Must be
non-negative and less than the size of the collection.
Parameter name: index"
At C:\Users\billkan\Documents\WindowsPowerShell\Modules\SQLServer\SQLServer.psm1:906 char:54
+       foreach ($perm in $server.EnumServerPermissions <<<< () | where {$_.PermissionType.ToStri
ng() -ne 'CONNECT SQL'})
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException
 


timestamp       : 2012-04-25
members         :
Xmlmembers      : <member></member>
Server          : xxxx
dbname          : master
PermissionType  : EXECUTE
Grantee         : ##MS_AgentSigningCertificate##
GranteeType     : User
Grantor         : dbo
GrantorType     : User
PermissionState : Grant
ColumnName      :
ObjectClass     : Database
ObjectName      : master
ObjectSchema    :
ObjectID        : 1

Coordinator
Apr 26, 2012 at 12:31 PM

What version and edition of SQL Server are you running the Get-SqlServerPermission against?

Apr 26, 2012 at 4:46 PM

I am running this against SQL Server 2012 instance. Thx Bill

Apr 26, 2012 at 4:57 PM

SQL Server 2012 Enterprise

Coordinator
Apr 26, 2012 at 8:51 PM

I haven't done any testing with SQL Server 2012. I'll try to in the next couple of days.

Apr 26, 2012 at 9:22 PM

Thank you. Appreciate your effort to make this work with SQL 2012. Thx Bill 

Coordinator
Apr 27, 2012 at 8:30 PM

I took a look at this  and the issue is related to assemblies being loaded. Fortunately the fix is easy. Just modify sqlpserver.psm1 located in the sqlserver foldere and replacing the assembly loading logic at the top of the script as follows. Note: I have not had a chance to thoroughly test on SQL 2012. If you see other issues let me know.

try {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
catch {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo"}

try {add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop; $smoVersion = 10}
catch {add-type -AssemblyName "Microsoft.SqlServer.Smo"; $smoVersion = 9}

try
{
    try {add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
    catch {add-type -AssemblyName "Microsoft.SqlServer.SMOExtended" -EA Stop}
}
catch {Write-Warning "SMOExtended not available"}

Apr 28, 2012 at 2:37 AM

Thank you, seems to work now. I have this in the script and used to run fine until I upgraded to SQL 2012. if I come across anything else, i will let you know.