Quickly Creating Test Users in SQL Server with PowerShell using the sqlserver module and dbatools

One of the most visited posts on my blog is nearly two and half years old now – Add User to SQL Server Database Role with PowerShell and Quickly Creating Test Users. I thought it was time to update it and use the latest sqlserver module and the dbatools module.

You can get the latest version of the sqlserver module by installing SSMS 2016. The PASS PowerShell Virtual Chapter have created a short link to make this easier for you to remember: https://sqlps.io/dl Once you have downloaded and installed SSMS you can load the module.

1
Import-Module sqlserver

There is one situation where you will get an error loading the sqlserver module into PowerShell. If you have the SQLPS module already imported then you will get the following error:

Import-Module : The following error occurred while loading the extended type data file:

sqlserver-module-error

In that case you will need to remove the SQLPS module first.

1
2
Remove-Module sqlps
Import-Module sqlserver

The original post dealt with creating a number of test users for a database and assigning them to different roles quickly and easily. First let’s quickly create a list of Admin users and a list of Service Users and save them in a text file.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
$i = 0
while ($I -lt 100) {
    "Beard_Service_User$i" | Out-File 'C:\temp\Users.txt' -Append
    $i++
}

$i = 0
while ($I -lt 10) {
    "Beard_Service_Admin_$i" | Out-File 'C:\temp\Admins.txt' -Append
    $i++
}

Now that we have those users in files we can assign them to a variable by using Get-Content

$Admins = Get-Content 'C:\temp\Admins.txt'

Of course we can use any source for our users

a database
an excel file
Active Directory
or even just type them in.

We can use the Add-SQLLogin command from the sqlserver module to add our users as SQL Logins, but at present we cannot add them as database users and assign them to a role. If we want to add a Windows Group or a Windows User to our SQL Server we can do so using:

1
Add-SqlLogin -ServerInstance $Server -LoginName $User -LoginType WindowsUser -DefaultDatabase tempdb -Enable -GrantConnectSql

Notice that we need to enable and grant connect SQL to the user.

If we want to add a SQL login the code is pretty much the same but we either have to enter the password in an authentication box or pass in a PSCredential object holding the username and password. Keeping credentials secure in PowerShell scripts is outside the scope of this post and the requirement is for none-live environments so we will pass in the same password for all users as a string to the script. You may want or be required to achieve this in a different fashion.

1
2
3
$Pass = ConvertTo-SecureString -String $Password -AsPlainText -Force
$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, $Pass
Add-SqlLogin -ServerInstance $Server -LoginName $User -LoginType $LoginType -DefaultDatabase tempdb -Enable -GrantConnectSql -LoginPSCredential $Credential

We can ensure that we are not trying to add logins that already exist using

1
2
if(!($srv.Logins.Contains($User)))
{

The $srv is a SQL Server Management Server Object which you can create using a snippet. I blogged about snippets here and you can find my list of snippets on GitHub here. However, today I am going to use the dbatools module to create a SMO Server Object using the Connect-DbaInstance command and assign the server and the database to a variable:

1
2
3
# Create a SQL Server SMO Object
$srv = Connect-DbaInstance  -SqlInstance $server
$db = $srv.Databases[$Database]

Once we have our Logins we need to create our database users:

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

and add them to a database role.

1
2
#Add User to the Role
$db.roles[$role].AddMember($User)

I created a little function to call in the script and then simply loop through our users and admins and call the function.

1
2
3
4
5
6
7
foreach ($User in $Users) {
    Add-UserToRole -Password $Password -User $user -Server $server -Role $Userrole -LoginType SQLLogin
}

foreach ($User in $Admins) {
    Add-UserToRole -Password $Password -User $user -Server $server -Role $adminrole -LoginType SQLLogin
}

To check that they have been added correctly I simply use the Get-DbaRoleMember;command from dbatools and output it to Out-GridView using the alias ogv as I am on the command line:

1
Get-DbaRoleMember -SqlInstance $server |ogv

which looks like this:

get-dbarole-memebr

Once we need to clean up the logins and users we can use the Get-SQLLogin and Remove-SQLLogin commands from the sqlserver module to remove the logins and if we do that first we can then use the dbatools command Remove-SQLOrphanuser to remove the orphaned users 🙂 (I thought that was rather cunning!)

1
2
(Get-SqlLogin -ServerInstance $server).Where{$_.Name -like '*Beard_Service_*'}|Remove-SqlLogin
Remove-SQLOrphanUser -SqlServer $Server -databases $database

The Remove-SQLLogin will prompt for confirmation and the result of the Remove-SQLOrphanUser looks like this

When you are looking at doing this type of automation with PowerShell, you should remember always to make use of Get-Command, Get-Help and Get-Member. That will enable you to work out how to do an awful lot. I have a short video on youtube about this:

and when you get stuck come and ask in the SQL Server Slack at https://sqlps.io/slack. You will find a powershellhelp channel in there. Here is the complete code:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
#Requires -module sqlserver
#Requires -module dbatools

### Define some variables
$server = ''
$Password = "Password"
$Database = 'TheBeardsDatabase'
$Admins = Get-Content 'C:\temp\Admins.txt'
$Users = Get-Content 'C:\temp\Users.txt'
$LoginType = 'SQLLogin'
$userrole = &nbsp; 'Users'
$adminrole = 'Admin'

# Create a SQL Server SMO Object
$srv = Connect-DbaSqlServer -SqlServer $server
$db = $srv.Databases[$Database]

function Add-UserToRole {
    param
    (
        [Parameter(Mandatory = $true,
            ValueFromPipeline = $true,
            ValueFromPipelineByPropertyName = $true,
            ValueFromRemainingArguments = $false)]
        [ValidateNotNullOrEmpty()]
        [string]$Password,
        [Parameter(Mandatory = $true,
            ValueFromPipeline = $true,
            ValueFromPipelineByPropertyName = $true,
            ValueFromRemainingArguments = $false)]
        [ValidateNotNullOrEmpty()]
        [string]$User,
        [Parameter(Mandatory = $true,
            ValueFromPipeline = $true,
            ValueFromPipelineByPropertyName = $true,
            ValueFromRemainingArguments = $false)]
        [ValidateNotNullOrEmpty()]
        [string]$Server,
        [Parameter(Mandatory = $true,
            ValueFromPipeline = $true,
            ValueFromPipelineByPropertyName = $true,
            ValueFromRemainingArguments = $false)]
        [ValidateNotNullOrEmpty()]
        [string]$Role,
        [Parameter(Mandatory = $true,
            ValueFromPipeline = $true,
            ValueFromPipelineByPropertyName = $true,
            ValueFromRemainingArguments = $false)]
        [ValidateSet("SQLLogin", "WindowsGroup", "WindowsUser")]
        [string]$LoginType
    )

    if (!($srv.Logins.Contains($User))) {
        if ($LoginType -eq 'SQLLogin') {
            $Pass = ConvertTo-SecureString -String $Password -AsPlainText -Force
            $Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, $Pass
            Add-SqlLogin -ServerInstance $Server -LoginName $User -LoginType $LoginType -DefaultDatabase tempdb -Enable -GrantConnectSql -LoginPSCredential $Credential
        }
        elseif ($LoginType -eq 'WindowsGroup' -or $LoginType -eq 'WindowsUser') {
            Add-SqlLogin -ServerInstance $Server -LoginName $User -LoginType $LoginType -DefaultDatabase tempdb -Enable -GrantConnectSql
        }
    }
    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
    $db.roles[$role].AddMember($User)
}

foreach ($User in $Users) {
    Add-UserToRole -Password $Password -User $user -Server $server -Role $Userrole -LoginType SQLLogin
}

foreach ($User in $Admins) {
    Add-UserToRole -Password $Password -User $user -Server $server -Role $adminrole -LoginType SQLLogin
}

Get-DbaRoleMember -SqlInstance $server | ogv

Happy Automating!