How to Create SQL Server Logins with PowerShell


By:   |   Updated: 2016-02-12   |   Comments (1)   |   Related: More > 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:

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.

Create SQL Logins with Powershell

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.


Last Updated: 2016-02-12


get scripts

next tip button



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

View all my tips
Related Resources




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, January 11, 2017 - 1:43:27 PM - Dolford Back To Top

Thank you. This is a quick and simple explanation that illustrates things nicely. Especially for someone like me who is still very new to PowerShell. This will allow me to automate. Great work!



download


Recommended Reading

Enabling xp_cmdshell in SQL Server

Encrypting passwords for use with Python and SQL Server

Understanding SQL Server fixed database roles

How to configure SSL encryption in SQL Server

Steps to Drop an Orphan SQL Server User when it owns a Schema or Role





get free sql tips
agree to terms


Learn more about SQL Server tools