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.