Inventory SQL Logins on a SQL Server with PowerShell

By:   |   Comments (5)   |   Related: > 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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

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

View all my tips

Comments For This Article

Wednesday, August 2, 2023 - 5:33:57 PM - doug Back To Top (91456)
OK. One more thing (yeah right :) )
$ returns just the names...

write-output $

Wednesday, August 2, 2023 - 5:23:38 PM - doug Back To Top (91455)
Me again:
Aside from all that, Great Article! Very handy! :)

Wednesday, August 2, 2023 - 5:22:09 PM - doug Back To Top (91454)
So, in the line that actually obtains the server.logins the script referenced $Instances but the var should be $InstanceNames
$Computer = ‘MySqlServerName’
foreach ($Instance in $InstanceNames) {
if ($Instance -eq 'MSSQLSERVER') {
$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $Computer
} else {
$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') "$Computer`\$Instance"

Wednesday, August 2, 2023 - 4:37:26 PM - doug Back To Top (91452)
So, after the line with the if, else block $server.logins should contain all logins on the DB servers, yes?

Wednesday, August 2, 2023 - 2:47:01 PM - doug Back To Top (91451)
I can see where this would be helpful but some things need clarity.
In the first line I think you meant $SqlServices = get-service...

Are each of the highlighted script samples supposed to be ran sequentially in a PowerShell session?
Would those be combined into one of the files you mentioned in final scriptlet?

get free sql tips
agree to terms