Inventory SQL Logins on a SQL Server with PowerShell


By:   |   Updated: 2016-02-19   |   Comments   |   Related: More > 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:

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


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




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.






download

























get free sql tips

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.



Learn more about SQL Server tools