Add User to SQL Server Database Role with PowerShell and Quickly Creating Test Users

There is a newer up to date version of this post here using the dbatools module and the sqlserver module

 

But if you want to continue with this way read on!!

Having created Windows Users or SQL Users using the last two days posts, today we shall add them to a role on a database.

As I discussed previously I believe that to follow good practice I try to ensure that database permissions are granted by role membership and each role is created with the minimum amount of permissions required for successful execution of the task involved.

So with each database having the correct roles created and the users created we just need to add the user to the database and to the role. This is easily done with PowerShell.

image

The Add-UserToRole function takes four parameters Server,Database,User and Role and does a series of error checks.

With these functions you can easily create a number of Users and add them to database roles quickly and easily and repeatedly.

If the test team come to you and require 10 Test Users and 3 Test Administrators adding to the test database. I create 2 notepad files

image  image

and use them with the Add-SQLAccountToSQLRole and Add-UserToRole functions to create the users

image

Here are the results in PowerShell

image

and in SSMS

image

The Code is here

#############################################################################################
#
# NAME: Add-UserToRole.ps1
# AUTHOR: Rob Sewell http://sqldbawithabeard.com
# DATE:11/09/2013
#
# COMMENTS: Load function to add user or group to a role on a database
#
# USAGE: Add-UserToRole fade2black Aerosmith Test db_owner
#        

Function Add-UserToRole ([string] $server, [String] $Database , [string]$User, [string]$Role)
{
$Svr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
#Check Database Name entered correctly
$db = $svr.Databases[$Database]
    if($db -eq $null)
        {
        Write-Output " $Database is not a valid database on $Server"
        Write-Output " Databases on $Server are :"
        $svr.Databases|select name
        break
        }
#Check Role exists on Database
        $Rol = $db.Roles[$Role]
    if($Rol -eq $null)
        {
        Write-Output " $Role is not a valid Role on $Database on $Server  "
        Write-Output " Roles on $Database are:"
        $db.roles|select name
        break
        }
    if(!($svr.Logins.Contains($User)))
        {
        Write-Output "$User not a login on $server create it first"
        break
        }
    if (!($db.Users.Contains($User)))
        {
        # Add user to database

        $usr = New-Object ('Microsoft.SqlServer.Management.Smo.User') ($db, $User)
        $usr.Login = $User
        $usr.Create()

        #Add User to the Role
        $Rol = $db.Roles[$Role]
        $Rol.AddMember($User)
        Write-Output "$User was not a login on $Database on $server"
        Write-Output "$User added to $Database on $Server and $Role Role"
        }
        else
        {
         #Add User to the Role
        $Rol = $db.Roles[$Role]
        $Rol.AddMember($User)
        Write-Output "$User added to $Role Role in $Database on $Server "
        }
}

 

3 thoughts on “Add User to SQL Server Database Role with PowerShell and Quickly Creating Test Users

  1. Pingback: Create SQL Logins and add SQL Roles | A Power Knight's journey

  2. Pingback: Quickly Creating Test Users in SQL Server with PowerShell using the sqlserver module and dbatools | SQL DBA with A Beard

  3. Pingback: Create SQL logins and add SQL Database roles – A Power Knight's journey

Please feel free to comment on this post. All comments are moderated first before appearing on the site

This site uses Akismet to reduce spam. Learn how your comment data is processed.