Powershell - SQL Server 2012 User Mapping

Question

I am trying to script User mapping for different accounts. I created a script to create users and individual server roles, but I can’t figure out how to install User Mapping with Powershell, I will also need to install Database Role membership , in particular db_backupoperator

Does anyone know how to do this with Powershell?

Answer 1

Suppose your username 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