PowerShell Pipeline
Creating a SQL Server Login Using PowerShell and SMO
PowerShell helps us to turn the time-intensive process of creating new users into a quick and easy task.
In a previous article, I talked about getting started with managing SQL server using PowerShell and the Server Management Objects (SMO). While that was mostly spent making the connection and doing some inspection of the PowerShell objects to report on various parts of SQL, I wanted to begin looking at things that you can use PowerShell and SMO to make changes to the instance such as managing access to a SQL server.
Today, we will be looking at creating a SQL login as well as dropping a login using PowerShell. First off, we will load up our required assemblies and then make the initial connection to the SQL server.
## 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.
All right, we are good to go with continuing to create some users on the SQL server.
First off, let's take a quick look at the existing users and see how many of the users are sqllogons and which ones are WindowsUsers or WindowsGroups.
PS C:\Windows\system32> ###Work with SQL Logins first
$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] 267 RIVENDELL\SQL_DBAs 9/12/2016 1:22:12 PM WindowsGroup
[vsql] 268 RIVENDELL\VWSUS$ 4/6/2017 10:02:24 PM WindowsUser
The first thing that I will start with is creating a SqlLogin on the server. To begin creating the account, I will need to figure out what the constructors are for the Microsoft.SqlServer.Management.Smo.Login object. Yea, it's a long type but that's ok. If you are running PowerShell V5, you can make use of the New() method which allows us to not only create an object, but view it's constructors if we leave out the parenthesis.
[Microsoft.SqlServer.Management.Smo.Login]::New
[Click on image for larger view.] Figure 2.
For this, I will go with providing the SqlServer object and providing a username for the account.
$SQLLogin = [Microsoft.SqlServer.Management.Smo.Login]::New($sqlServer, 'TestUser')
I need to save the resulting object so that I can continue to update some properties on it. If I attempt to view the object now, it will throw errors about needing to update some properties. No worries there, we can still update properties and then view the object later on.
Since I am creating a SqlLogin login type, I need to update the LoginType property to show it being a SqlLogin. In this case, I am taking advantage of the LoginType enum. I am also setting the PasswordPolicyEnforced to False just to prevent any issues with the creation of the account.
$SQLLogin.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin
$SQLLogin.PasswordPolicyEnforced = $False
We are not quite done yet! We need to call the Create() which will create the account and place it as a SQL server logon. There are a few overload definitions with the Create method that we should look at and decide on using.
$SQLLogin.Create
[Click on image for larger view.] Figure 3.
In this case, I will go the simple route by supplying a clear text password for the account.
$SQLLogin.Create('SuperSecretPassword,DontTell!')
Now the account has been created and is now available to view in SQL. I'll refresh my list of SQL logins and check to see if it now visible.
PS C:\Windows\system32> #Verify SQL Logon created
$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] 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] 270 TestUser 10/25/2017 8:43:33 PM SqlLogin
[vsql] 259 VSQL\Administrator 9/12/2016 1:06:34 PM WindowsUser
We can see that our TestUser has been created as a SqlLogin and is now available in our SQL server logins. Let's do one more account creation by this time using a domain account.
$SQLWindowsLogin = [Microsoft.SqlServer.Management.Smo.Login]::New($sqlServer, 'rivendell\admin.prox')
$SQLWindowsLogin.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::WindowsUser
$SQLWindowsLogin.Create()
In this example, I set my LoginType as a 'WindowsUser' instead of a SqlLogin and left out updating the password policy since this is a domain account. A quick refresh of logins and we can see that our windows account is now available.
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] 270 TestUser 10/25/2017 8:43:33 PM SqlLogin
[vsql] 259 VSQL\Administrator 9/12/2016 1:06:34 PM WindowsUser
The last thing left to do is to drop the SqlLogin that I created. I can do this by locating the login as shown in the example below.
$ToDrop = $sqlServer.Logins['TestUser']
[Click on image for larger view.] Figure 4.
Once I have the account, I simply call the Drop() method and our account is no longer available!
[Click on image for larger view.] Figure 5.
No more TestUser SqlLogin!
As I have shown, we can use PowerShell with SMO to manage our SQLLogins on SQL and you can take this further by using this technique and a CSV (or similar file) to make quick work of login creations with just a few lines of code!