Tuesday, September 23, 2008

Scripting role members in every database using PowerShell.

Starting this post I will be writing in English, in order to reach the users from the PowerShell Community.

Previously, I shared some scripts written in PowerShell to generate automatically some configurations (like users, jobs, linked servers, etc) in SQL Server 2005 using SMO with PowerShell.

In this post, I wrote a script to generate the users in every database, that script had an error, the script generated needed a GO command before the instruction to add the user to their respective role.

So, I created this script to generate a script with the sys.sp_addrolemember instruction for every role,it is helpful if you want to restore any database in other server (you would need first to recreate the users with the other script) or in case a Disaster Recovery scenario.

So, this is the script:

param (
[string] $serverName
$Scripts = "c:\scripts\"

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$MyScripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter")
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" $serverName

foreach($db in $srv.databases) {
if ($db.Name -ne "master" -and $db.Name -ne "model" -and $db.Name -ne "msdb" -and $db.Name -ne "tempdb") {
$f = [System.IO.Path]::Combine($Scripts, $srv.Name +"_roles_" + $db.Name + ".sql")
out-file -filePath $f -inputobject "USE $db"
out-file -filePath $f -inputobject "GO" -Append
foreach ($role in $db.Roles) {
if ($role.Name -ne "public") {
foreach ($member in $role.EnumMembers()) {
if ($member -ne "dbo") {
$var = "sys.sp_addrolemember @rolename = N'" + $role.Name+"', @membername = N'"+$member+"'"
out-file -filePath $f -inputobject $var -append
out-file -filePath $f -inputobject "GO" -append

To run this script, you just need to specify the sql server and it will generate in c:\scripts a file for every database containing the script to add the members for every user, just like this:

sys.sp_addrolemember @rolename = N'HR', @membername = N'user1'
sys.sp_addrolemember @rolename = N'sales', @membername = N'user2'

No comments: