THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Use PowerShell to Add a Login to a Database Role in all Databases

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

Published Friday, June 03, 2011 2:09 PM by AllenMWhite

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Andrew Wickham said:

Instead of the long section where you manually execute the sp_addrolemember stored procedure, check out the AddMember method on the $db.Roles object.

You can change the entire section...

$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()

To just...

$db.Roles['db_backupoperator'].AddMember($logname)

June 3, 2011 3:49 PM
 

Mark Freeman said:

I appreciate your time to craft this solution. I modified a suggestion from @SQLSoldier (Robert L Davis) as follows:

EXEC sp_msForEachDB 'Use ?; If Not Exists(Select 1 From sys.database_principals Where Name = ''[User]'') Create User [User] For Login [User]; EXEC sp_addrolemember N''db_backupoperator'', N''[User]'''

This works for me, and seems much simpler than wrapping it in the Powershell code, given that T-SQL is required anyway.

June 3, 2011 5:23 PM
 

Denise said:

Hi, Ellen,

I am trying and learning to use ps to restore data.bak file to Sql Server 2008 in powershell.  So far I didn't have any luck. I'm having an errors, would you tell me where to debug?

--Errors:

C:\Backup Files\New folder\AppsAdmin.bak

File

Exception calling "ReadBackupHeader" with "1" argument(s): "Failed to connect to server DENISE-PC."

At line:10 char:50

+ $smoRestoreDetails = $smoRestore.ReadBackupHeader <<<< ("DENISE-PC")

   + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

   + FullyQualifiedErrorId : DotNetMethodException

Cannot index into a null array.

At line:12 char:25

+ $smoRestoreDetails.Rows[ <<<< 0]["AppsAdmin"]

   + CategoryInfo          : InvalidOperation: (0:Int32) [], RuntimeException

   + FullyQualifiedErrorId : NullArray

--Code for restore

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

| Out-Null

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")

| Out-Null

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")

| Out-Null

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")

| Out-Null

$backupFile = 'C:\Backup Files\New folder\AppsAdmin.bak'

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(DENISE-PC)"

$backupDevice =

New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem")

($backupFile, "File")

$smoRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")

$smoRestore.NoRecovery = $false;

$smoRestore.ReplaceDatabase = $true;

$smoRestore.Action = "Database"

$smoRestorePercentCompleteNotification = 10;

$smoRestore.Devices.Add($backupDevice)

$smoRestoreDetails = $smoRestore.ReadBackupHeader("DENISE-PC")

"Database Name from Backup Header : " +

$smoRestoreDetails.Rows[0]["AppsAdmin"]

$smoRestore.Database =$smoRestoreDetails.Rows[0]["AppsAdmin"] + "AppsAdmin_Copy"

$smoRestoreFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")

$smoRestoreLog = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")

$smoRestoreFile.LogicalFileName = $smoRestoreDetails.Rows[0]["AppsAdmin"]

$smoRestoreFile.PhysicalFileName = $server.Information.MasterDBPath + "\" + $smoRestore.Database + "AppsAdmin_Data.mdf"

$smoRestoreLog.LogicalFileName =

$smoRestoreDetails.Rows[0]["AppsAdmin"] + "AppsAmin_Log"

$smoRestoreLog.PhysicalFileName = $server.Information.MasterDBLogPath + "\" + $smoRestore.Database + "_Log.ldf"

$smoRestore.RelocateFiles.Add($smoRestoreFile)

$smoRestore.RelocateFiles.Add($smoRestoreLog)

$smoRestore.SqlRestore("DENISE-PC")

June 9, 2011 9:54 AM
 

Eric said:

Hi,

do you have an exemple to create a new windows login in SQLServer

[DomainNAme\LoginName]

thank's

June 29, 2011 5:23 AM
 

kin said:

do you know if I can use powershell to connect to sybase and execute any sp like sp_helpdb or sp_lmconfig ?

Cheers,

February 14, 2012 4:37 PM

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement