Why am I asking?

Have you never have been in a situation where:

• the master backup is corrupted
• you need to rollback a password change
• know the list of permissions on a specific day (the scenario: “yesterday was OK, today is not working” due permission changes)

Do you see the pain?!

If you are not seeing the hard work yet, I will try to highlight some details. If you need to restore a database, this is not a big deal since the backup is OK. By the way, have you been testing your backups? See some automated examples from Chrissy LeMaire (t) here and from Sander Stad (t) here.

With a database restore, the users are within a database and if their SID matches the SQL Login you are ready to go. But with the logins it is a different story!

If you have to reinstall the engine because your master database backup is corrupt or someone hs changed the login password and you want to put it back or even – maybe the most common scenario – you want to keep track of the login permissions you need to have them saved somewhere.

Imagine that you have to re-create a login and give all the permissions that it has, imagine that this login has multiple roles on different databases. Did you know that beforehand? Do you keep track of it? How much time would take to gather all that information from the application owner? How much time will you have to spend resolving all the permission issues until everything is running smoothly? How many tickets will need to be raised? How many users will this be affecting?

“Ok, now you got my attention! How can I accomplish that task?”

As with so many other tasks, there is not just one way of doing it. I will provide you with the method that I prefer. I have automated this and run it on a daily schedule getting the logins from all of my instances.

I will use dbatools – If you don’t know it is an open source PowerShell module that makes a DBA’s life easier with more than 200 commands.

You just need to download and install dbatools module. For our objective we will use the Export-SqlLogin command.

To accomplish the task you can use the following script to output one file per instance with all the logins from that instance.

#Requires -module dbatools
#Get a list of instances where you will run the command through
$SQLServers = Invoke-Sqlcmd -ServerInstance "sql2008" -Query "SELECT InstanceConnection FROM CentralDatabase.dbo.Connections"
#If the folder does not exists create it
$newFolder = "C:\temp\ExportLogins\$(Get-Date -f MM-dd-yyyy_HH_mm_ss)"
if ((Test-Path -LiteralPath $newFolder) -eq $false) {
    New-Item -Type Directory -Path $newFolder
}
#For each instance 
$SQLServers | Foreach-Object {
    
    #generate a filename
    $fileName = $_.InstanceConnection -replace ",", "_"
    $fileName = $fileName -replace "\\", "_"
    #run the Export-SqlLogin command
    Export-SqlLogin -SqlInstance $_.InstanceConnection -FilePath "$newFolder\$fileName.sql" 
}

By executing this script you will get the list of connection strings from your central database, in this example “CentralDatabase” on “SQL2008” SQL Server instance. If you use the CMS (Central Management Server) you can change the code and get your list of servers using the Get-SqlRegisteredServerName command.

List of instances

Next, we will run the command for each instance. On line 16 and 17 we do a couple of replaces to change the comma (,) and backslash (\) from InstanceConnection value, because those are invalid characters for file names, with the underscore character (_) .

Finally we have the call to the Export-SqlLogin where we use the $_.InstanceConnection value and the output file using -FilePath parameter and in this case, we will be generating the logins into a file in the C:\temp\ExportLogins\.

This is what the folder looks like after executing the script:

Let’s see it running:

All the issues we talked about at the beginning are now easily resolved because you can open the script and run it (all or just a block) to quickly fix it!

Of course, make sure that you keep this folder very secure!

Thank you for reading!

NOTE – The major 1.0 release of dbatools due in the summer 2017 may have breaking changes which will stop the above code from working. There are also new commands coming which may replace this command. This blog post was written using dbatools version 0.8.957 You can check your version using

Get-Module dbatools

and update it using an Administrator PowerShell session with

Update-Module dbatools

You may find that you get no output from Update-Module as you have the latest version. If you have not installed the module from the PowerShell Gallery using

Install-Module dbatools

Then you can use

Update-dbatools