DBATOOLS: Get-DbaLogin Find Database logins

For a quick way to search for specific database logins across your data estate, the easiest way is to use the dbatools command: Get-DbaLogin.

If you are using SQL Server authentication, you may have to change the password for one of your database accounts. If you have 1 or 2 servers, you can just login to each server and see if the user exists.

Below I login and see that the ETL user exists. This is one way to do it.

A potential faster way to look up one account is to use Get-DbaLogin by dbatools. if you do not know what dbatools is, please read my first article about this huge time saving tool. https://jimsalasek.com/2021/10/05/dbatools-io-a-dba-timesaver-luxury/

Ok, by using PowerShell, you can easily find if an user exists. The command to check for a SQL Server authenticated user is as follows:

get-dbalogin -sqlinstance etlsqldb -login etl

You need to specify the instance, ETLSQLDB in my case, and what login you are searching for. ETL in our example.

You can also search for Windows authenticated users using this command:

get-dbalogin -sqlinstance etlsqldb -type windows -includefilter *salasek*

You need to specify type of Windows, and you filter by name using the wildcard * around the name you are searching for.

You may not think this is very efficient, but you can search multiple servers at the same time!

First you need to add a list of servers in your PowerShell profile. To find where your profile exists, type in the following command.

$PROFILE|format-list -force

This shows you where your profile lives. I am going to modify the CurrentUserAllHosts one that lives in my users directory.

After opening up the profile.ps1 file in a text editor, add a new line that begins with $. You can pick any name, but I am naming mine $ServerName. Then list all of your servers separated by double quotes and a comma, then save the file.

I usually restart PowerShell so it reads the new profile.

Now you can specify the $ServerName for the instance name and it will check every server in the list and display the results. Below I am passing $ServerName in. I am also outputting the results to a grid by using the “|out-gridview” command at the end.

get-dbalogin -sqlinstance $ServerName -login etl |out-gridview

To check all of these servers took approx 5 seconds. To do this manually, it may have took 30-60 seconds per server to login, and then drill down through security and check if the user exists.

Here is what the results look like using |out-gridview. Now you know which servers the username is on.

For more on all of the time saving commands that DBATOOLS provide, go to their website and see the more than 500 commands that are available. https://dbatools.io/