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\'

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") 

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\'
[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") 




About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in PowerShell and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s