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.

By Boe Prox

11/30/2017

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.

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.

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.

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.

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.

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!