Powershell - User Mapping SQL Server 2012

Question

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?

Nolitta

Answer 1

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

Exclusive

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. – Nolitta

What is the difference between login name and dbUserName? – Megrim