PowerShell Pipeline

Working with Database Users and Roles Using PowerShell

During the last few articles, we have covered the basics of working with SQL server using the Server Management Objects (SMO) and PowerShell. We have covered things such as viewing the state of databases, backups and even creating SQL server logins. Today we are extending our look at user accounts by creating database users and then adding them to specific roles for to provide the proper level of access to a database.

First thing is first: we need to load up our assemblies and then create our connection to our SQL server, vsql.

## Add Assemblies 
Add-Type -AssemblyName  "Microsoft.SqlServer.ConnectionInfo,  Version=,  Culture=neutral, PublicKeyToken=89845dcd8080cc91"  -ErrorAction Stop
Add-Type -AssemblyName  "Microsoft.SqlServer.Smo, Version=,Culture=neutral,PublicKeyToken=89845dcd8080cc91"  -ErrorAction Stop
Add-Type -AssemblyName  "Microsoft.SqlServer.SMOExtended,  Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91"  -ErrorAction Stop
Add-Type -AssemblyName  "Microsoft.SqlServer.SqlEnum,  Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91"  -ErrorAction Stop
Add-Type -AssemblyName  "Microsoft.SqlServer.Management.Sdk.Sfc, Version=,Culture=neutral,  PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

#Connect to the SQL Server
$sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server -ArgumentList 'vsql'

And we will verify that our connection is good to the server.

[Click on image for larger view.] Figure 1.

Now that we have this out of the way, we can continue with taking an existing SQL server login and adding it as a database user.

We know that we have a number of SQL server logins by running the following code:


PS C:\Windows\system32>  
$sqlServer.Logins |
Format-Table -Property Parent, ID, Name, CreateDate, LoginType

Parent  ID Name                              CreateDate               LoginType
------  -- ----                              ----------               ---------
[vsql] 256 ##MS_PolicyEventProcessingLogin## 2/20/2014 8:49:46 PM      SqlLogin
[vsql] 257 ##MS_PolicyTsqlExecutionLogin##   2/20/2014 8:49:46 PM      SqlLogin
[vsql]   1 adminSQL                          4/8/2003 9:10:35 AM       SqlLogin
[vsql] 264 NT AUTHORITY\SYSTEM               9/12/2016 1:06:35 PM   WindowsUser
[vsql] 263 NT Service\MSSQLSERVER            9/12/2016 1:06:35 PM   WindowsUser
[vsql] 266 NT SERVICE\ReportServer           9/12/2016 1:06:53 PM   WindowsUser
[vsql] 265 NT SERVICE\SQLSERVERAGENT         9/12/2016 1:06:35 PM   WindowsUser
[vsql] 261 NT SERVICE\SQLWriter              9/12/2016 1:06:35 PM   WindowsUser
[vsql] 262 NT SERVICE\Winmgmt                9/12/2016 1:06:35 PM   WindowsUser
[vsql] 269 proxb                             5/7/2017 3:35:41 PM       SqlLogin
[vsql] 271 rivendell\admin.prox              10/25/2017 8:46:32 PM  WindowsUser
[vsql] 267 RIVENDELL\SQL_DBAs                9/12/2016 1:22:12 PM  WindowsGroup
[vsql] 268 RIVENDELL\VWSUS$                  4/6/2017 10:02:24 PM   WindowsUser
[vsql] 259 VSQL\Administrator                9/12/2016 1:06:34 PM   WindowsUser

I think today I will use my admin.prox SQL login and grant it access to one of the databases on the server. I should take a look at what databases I have on this server so I know what I am working with here.


PS C:\Windows\system32> $sqlServer.Databases | 

Format-Table Name,Owner,Status,RecoveryModel

Name               Owner                Status RecoveryModel
----               -----                ------ -------------
master             adminSQL             Normal        Simple
model              adminSQL             Normal          Full
msdb               adminSQL             Normal        Simple
ReportServer       adminSQL             Normal          Full
ReportServerTempDB adminSQL             Normal        Simple
ServerInventory    rivendell\admin.prox Normal          Full
SUSDB              rivendell\admin.prox Normal        Simple
tempdb             adminSQL             Normal        Simple

Based on the provided information, I will look to grant access to the ServerInventory database to Admin.Prox. We will now use a similar technique that I covered when I created the SQL server login to create the database user account on the ServerInventory database.

But before we do that, let's quickly look at who has access to the database now.


$Database.Users | Select Name, LoginType

Name               LoginType
----               ---------
dbo                 SqlLogin
guest               SqlLogin
proxb               SqlLogin
sys                 SqlLogin

Right now we only have five SqlLogins that are created on the ServerInventory database. Now we will begin to create the database user account on the database using an existing SqlLogin.

I first need the database object which will be supplied to a constructor in creating a user account.

$Database = $sqlServer.Databases['ServerInventory'] 

Now that I have this information, I will create the user object using the database object and supplying a string username of the SqlLogin that already exists. During this time, I will also set the login name of the user account.

$DBUser = [Microsoft.SqlServer.Management.Smo.User]::New($Database, 'Rivendell\Admin.Prox')
$DBUser.Login = 'Rivendell\Admin.Prox'

Now we will go back and verify that the database user account has in fact been created on the database.

PS C:\Windows\system32> $Database.Users.Refresh()
$Database.Users | Select Name, LoginType

Name                   LoginType
----                   ---------
dbo                     SqlLogin
guest                   SqlLogin
proxb                   SqlLogin
Rivendell\Admin.Prox WindowsUser
sys                     SqlLogin

As expected, the database user account has now been created. The only thing is that our database user currently has no roles on the database, as noted when we call EnumRoles() on the user object.


We should at least provide DBDataReader access so the account can view the tables of the database for their job. Note, I can quickly refresh myself on the proper naming of each role using the following line of code:

$Database.Roles  | Select Name 
[Click on image for larger view.] Figure 2.

Knowing this, I can add my database user to the db_datareader role and the follow up with verification that the database user is in the role using the following lines of code:

[Click on image for larger view.] Figure 3.

Looks good to me! Now if we wanted to drop the database user from the database, we can use the same Drop() method that we are familiar with from dropping a SQL login from our previous article.


Looking at our database users, we can see that the account is no longer available on the database.

PS C:\Windows\system32> $Database.Users.Refresh()
$Database.Users | Select Name, LoginType

Name               LoginType
----               ---------
dbo                 SqlLogin
guest               SqlLogin
proxb               SqlLogin
sys                 SqlLogin

And with that, we are done for the day with working with adding a database user along with specifying a database role for the user.

About the Author

Boe Prox is a Microsoft MVP in Windows PowerShell and a Senior Windows System Administrator. He has worked in the IT field since 2003, and he supports a variety of different platforms. He is a contributing author in PowerShell Deep Dives with chapters about WSUS and TCP communication. He is a moderator on the Hey, Scripting Guy! forum, and he has been a judge for the Scripting Games. He has presented talks on the topics of WSUS and PowerShell as well as runspaces to PowerShell user groups. He is an Honorary Scripting Guy, and he has submitted a number of posts as a to Microsoft's Hey, Scripting Guy! He also has a number of open source projects available on Codeplex and GitHub. His personal blog is at http://learn-powershell.net.

comments powered by Disqus

SharePoint Watch

Sign up for our newsletter.

I agree to this site's Privacy Policy.