Mark Freeman (@m60freeman) posted a question on Twitter using the #sqlhelp hashtag today, asking "Does anyone have a script to assign db_backupoperator for all databases in an instance to a login?" I looked and didn't really have anything that fit, but felt it'd be useful to understand the SMO process to create it.

Note: one glaring hole I've found in SMO is the inability to add or remove logins and users to system and database roles, so that has to be done via T-SQL, as you'll see.

First, I get the instance and login names as command-line arguments, load the SMO library, and connect to the instance using my Windows Authentication.

# Get the SQL Server instance name from the command line
param (
[string]$inst,
[string]$login
)
# Load the SMO assembly
[System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO') | out-null
# Connect to the instance using SMO
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst

The next thing I do is check to see if the login already exists. If not, report it and get out of the script.

# Get the defined login - if it doesn't exist it's an error
$log = $s.Logins[$login]
if ($log -eq $null) {
write-output "$login is not a valid SQL Server Login on this instance."
break;
}

We have a valid login, so cycle through the databases on the instance, and check if the login is a valid user. If not, add it by creating a new User object, set the User's Login property to the login name, and Create the user.

# Cycle through the databases
foreach ($db in $s.Databases) {
$dbname = $db.Name
$logname = $log.Name

# Check to see if the login is a user in this database
$usr = $db.Users[$logname]
if ($usr -eq $null) {

# Not present, so add it
$usr = New-Object ('Microsoft.SqlServer.Management.Smo.User') ($db, $logname)
$usr.Login = $logname
$usr.Create()
}

Once we have a valid user in the database, check to see if the user is already a member of the role. (In this case, the db_backupoperator role.) If not, connect to the database using ADO.NET, pass the sp_addrolemember query to add it to the SqlCommand object and execute the query. Using this method allows me to execute the script whether or not I've got the SQL snapins loaded.

# Check to see if the user is a member of the db_backupoperator role
if ($usr.IsMember('db_backupoperator') -ne $True) {

# Not a member, so add that role
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=$inst;Integrated Security=SSPI;Initial Catalog=$dbname");
$cn.Open()
$q = "EXEC sp_addrolemember @rolename = N'db_backupoperator', @membername = N'$logname'"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($q, $cn)
$cmd.ExecuteNonQuery() | out-null
$cn.Close()

}
}

Now, the other thing I might want to do is to test the database's IsSystemObject property, and perhaps exclude the system databases by testing if that's true or false.

Yes, there are a number of ways to accomplish this, but this gives you one more tool in the belt.

Allen