8

I am trying to script User Mapping for different Login accounts. I have scripted the creation of users and individual server roles, but I can't figure out how to set User Mapping with Powershell, I will also need to set the Database Role membership, in Particular, db_backupoperator

Anyone know how to do this with Powershell?

enter image description here

JMeterX
  • 438
  • 1
  • 8
  • 19

1 Answers1

11

Supposing your login is created

## Creating database user and assigning database role    

#get variables
$instanceName = "yourInstance"
$loginName = "testLogin"
$dbUserName = "testUserName"
$databasename = "tempdb"
$roleName = "db_backupoperator"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

#add a database mapping
$database = $server.Databases[$databasename]
$login = $server.Logins[$loginName]
if ($database.Users[$dbUserName])
{
    $database.Users[$dbUserName].Drop()
}
$dbUser = New-Object `
-TypeName Microsoft.SqlServer.Management.Smo.User `
-ArgumentList $database, $dbUserName
$dbUser.Login = $loginName
$dbUser.Create()

#assign database role for a new user
$dbrole = $database.Roles[$roleName]
$dbrole.AddMember($dbUserName)
$dbrole.Alter
Aleksandr Fedorenko
  • 16,594
  • 6
  • 37
  • 44
  • Soo, this works, just as my previous attempt did. It will set the User Mapping up for the specified database, however, it will have the `dbUserName` as the User, I want the `login` which in my case is a group to be the User...I will add the code in for what I have. – JMeterX Aug 27 '13 at 18:25
  • What is the difference between login name and dbUserName? – Demodave Nov 18 '16 at 16:23