Inventory SQL Logins on a SQL Server with PowerShell

By:   |   Updated: 2016-02-19   |   Comments   |   Related: More > Security


Have you ever had the need to get an inventory of all of the logins that have been created on a bunch of SQL servers? I have and it wasn't pretty. I was trying to deploy the System Center Operations Manager client across a datacenter and if you're familiar with that product it needs a SQL login to monitor the databases in a SQL instance. After connecting to each of these servers with SQL Server Management studio, clicking the Logins box and noting which logins existed on all of these servers I gave up and decided to script it with PowerShell.


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 finding SQL logins on your SQL servers much easier.

Before we can reliably find SQL logins on a SQL Server we first enumerate all instances on that server. One way to do this is to simply query all Windows services on that server starting with the name of "SQL Server (". To find these services, I'll use the Get-Service cmdlet.

SqlServices = Get-Service -ComputerName MYSERVER -DisplayName 'SQL Server (*'

Once I've got all of the SQL services, I can then pull out the instance names from the service names using a calculated property with Select-Object.

$InstanceNames = $SqlServices | Select-Object @{ n = 'Instance'; e = { $_.DisplayName.Trim('SQL Server ').Trim(')').Trim('(') } } | Select-Object -ExpandProperty Instance

Now I have all of the instances on that server. I can now query each of those instances and look for all of the SQL logins inside each instance. But first, since I'll be instantiating a SMO object a little later I need to ensure the SMO .NET type is available so I will add this first.


Next, I'll loop through each of those instance names found and instantiate a new Microsoft.SqlServer.Management.Smo.Server object using the server name and the default MSSQLSERVER instance as a parameter to the object or, if additional instances were found, specify the computer name as well as the instance name when building the SMO object.

$Computer = 'MYSQLSERVER'
foreach ($Instance in $Instances) {
	if ($Instance -eq 'MSSQLSERVER') {
	$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $Computer
	} else {
	$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') "$Computer`\$Instance"

In the example above, the script is checking each of the instance names found earlier by querying Window service names. If the instance name is the default MSSQLSERVER, it's building the SMO object with no instance as a parameter. If it's something other than MSSQLSERVER it's then creating the SMO object with the server name and the instance name. As part of the Microsoft.SqlServer.Management.Smo.Server object called $Server, it has a property called Logins. This property contains all of the SQL logins that have been build on that server.

This is great for a single server but what if you'd like to do this on multiple servers? To do this and ease the process of finding these logins, I've built a PowerShell module that you can use to perform these tasks on one or multiple SQL Servers at once simply called SQLLogin. After downloading this module, place it into a folder in your $env:PSModulePath environment variable and simply use Get-SQLLogin and point it at one or more of your SQL servers.

For example, to find all SQL logins on a bunch of SQL servers in a text file you could do something like this:

Get-Content C:\SQLServers.txt | Get-SQLInstance | Get-SQLLogin

This will enumerate all of the instances on all servers in the text file C:\SQLServers.txt, find all instances on those servers and find all logins within each of those instances.

Next Steps

Last Updated: 2016-02-19

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 (*).

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.


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