Inventory SQL Logins on a SQL Server with PowerShell

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

Problem

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.

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 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.

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

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"
		}
		$Server.Logins
}

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

• 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