PowerShell : Script Method Error -Method invocation failed because [Microsoft.SqlServer.Management.Smo.ServerRole] does not contain a method named ‘Script’.

I was working on generating scripts for logins, users and server roles and I could able to to generate scripts for users and logins but where as for server roles, the script() method is not working.

The script is throwing following error

Method invocation failed because [Microsoft.SqlServer.Management.Smo.ServerRole] does not contain a method named ‘Script’.
At line:3 char:23
+ $srv.Roles | foreach {$_.Script()+ “GO”}
+ ~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound

In any such cases the easiest way to identify the problem by issuing the Get-Member cmdlet. It tells you the property and method for the current object.

Using Get-Member, It din’t list script() method that gives me an answer to look for a workaround.

$directoryname= 'E:\Output\'
$sqlserver='AQDBSP18'

Function get-roles {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.Roles|Get-Member| Where-Object {$_.name -like 'script*'} 
#foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "_roles.sql") 
}
#get-roles

Function get-userlogins {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.Logins |Get-Member |Where-Object {$_.name -like 'script*'}
#| foreach {$_.Script()+ "GO"}

#| Out-File $($directoryname + $serverfilename + "_logins.sql") 
}

Workaround for generating script for server roles

$directoryname= 'E:\Output\'
$sqlserver='AQDBSP18'
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$srv =  New-Object ("Microsoft.SqlServer.Management.SMO.Server") ($sqlserver)
$srv.Logins|Where-Object {$_.IsSystemObject -eq $false -and $_.loginType -like  "windows*"} | foreach { $_.Script() +"GO" | Where {$_ -notlike 'ALTER LOGIN*DISABLE'}
#|Out-File $($directoryname + $sqlserver + "_logins.sql") 
}

 #| Out-File $($directoryname + $sqlserver + "_logins.sql") 
foreach ($Role in $srv.Roles){$Role.EnumServerRoleMembers() | Where-Object {$_ -ne "sa"} |% {"EXEC master..sp_addsrvrolemember @loginame = N'{0}', @rolename = N'{1}'{2}" -f ($_,$Role.Name,"`r`nGO");}
#|Out-File $($directoryname + $sqlserver + "_roles.sql") 
};

 

Advertisements