How to Create SQL Server Logins with PowerShell

By: Adam Bertram   |   Comments (1)   |   Related: > Security

Problem

Creating SQL logins on a single SQL Server is easy, you can simply open up SQL Server Management Studio, right click on the Security folder and choose new login. That's fine for a single server, but what if you've got to create logins on dozens or hundreds of SQL instances? It's time to script it and we'll use PowerShell.

Solution

To query any SQL Server database from PowerShell you need to have the SQLPS module installed along with a few supporting packages to make your life easier. I'm going to be focusing on the SQL Server 2012 packages in this tip. You will need:

• Microsoft. System CLR Types for Microsoft. SQL Server. 2012
• Microsoft. SQL Server. 2012 Shared Management Objects
• Microsoft. Windows PowerShell Extensions for Microsoft. SQL Server. 2012

Once you have these packages on your system you're ready to start making your life of creating SQL logins on your SQL Servers much easier.

Before we can create logins on multiple SQL Servers we first need to script a single one. To do this, I'll need to ensure the SMO .NET type is available, so I will load this first into my session.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

Next, we'll need to instantiate a new Microsoft.SqlServer.Management.Smo.Server .NET object in order to execute methods on this object thus communicating with the SQL Server.

$Server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') MYSERVER

In the above example, I'm creating the object and passing my SQL Server name of MYSERVER to it.

To create SQL logins with PowerShell, each login must be an object of type Microsoft.SqlServer.Management.Smo.Login. To build this object, we can simply use the New-Object cmdlet again and specify the object type and specify the server object we created earlier and the username.

$Login = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArgumentList $Server, $Username 

Once the login object is created, we'll then need to specify a login type. This login type can be a number of different entities, but we're creating SQL logins so we'll choose a type of SqlLogin. This value will be assigned to a property of the login object created earlier called LoginType.

$Login.LoginType = 'SqlLogin'

At this point, the SQL login has been created!

Now that you've got an idea of how to create a single SQL login on a single SQL Server, let's take it up a notch. I'm now going to show you how you can create multiple SQL logins on multiple SQL Servers.

Let's say you have a CSV file with a list of SQL logins you would like to create on a number of SQL Servers. This CSV file has two columns; Login and Server. I want to create the SQL logins associated with each server.

I first need to read the CSV file I created (SQLLogins.csv) and each row therein. I can do this with the Import-Csv cmdlet. I will assign every row to the $rows variable.

$rows = Import-Csv Path C:\SQLLogins.csv

Once I've have all of the rows stored, I will use a foreach loop and read each row. Inside of that loop, I will mirror exactly what I did for a single server, but will replace the static login and server names with variables to represent the values inside of the CSV file.

The entire script will look something like this:

$rows = Import-Csv Path C:\SQLLogins.csv
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
foreach ($login in $rows) {
	$Server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $login.Server
	$Login = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArgumentList $Server, $login.Login
	$Login.LoginType = 'SqlLogin'
}

Notice how instead of specifying a static server name like MYSERVER I'm using the current row's value of Server by using $login.Server and instead of statically specifying the username I'm using the current row's value of Login here by using $login.Login.

Once run, this script will create each login associated with every server in that CSV file in no time flat!

Next Steps

• If you'd like this sample code already bundled up into a single PowerShell module, I've created a module called SQLLogin click here. Simply copy and paste this into a text file and save it with a PSM1 extension. It contains a function called New-SqlLogin that you use to quickly create SQL logins on your SQL Servers.
• Check out all of the SQL Server Security tips.
• Check out all of the PowerShell tips.

About the author

Adam Bertram is an independent consultant, technical writer, trainer, and presenter. Adam specializes in consulting and evangelizing all things IT automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips