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=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=12.0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
Add-Type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
Add-Type -AssemblyName "Microsoft.SqlServer.SqlEnum, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
Add-Type -AssemblyName "Microsoft.SqlServer.Management.Sdk.Sfc, Version=12.0.0.0,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.Refresh()
$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
INFORMATION_SCHEMA 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'
$DBUser.Create()
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
INFORMATION_SCHEMA 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.
$DBUser.EnumRoles()
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:
$DBUser.AddToRole('db_datareader')
$DBUser.EnumRoles()
[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.
$DBUser.Drop()
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
INFORMATION_SCHEMA 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.