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

scriptMethod1

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

 

scriptMethod2

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek 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