Friday, 28 July 2017

Cleanup SQL Server Logins

Cleaning up after someone leaves. We all do it right? Our companies all have water tight policies and procedures to handle that, or maybe they have policies but not procedures. Or maybe they just kind of don't.

Well, if your company is anything like most of the places I've worked, it's somewhere in the middle. And from a DBA perspective, especially on non-production environments, your list of logins and users becomes a bit long and messy after a while.

I've put together the PowerShell script below to get a list of Active Directory accounts as SQL logins and then checks for their existence in Active Directory. The script then generates SQL to drop users and logins. The SQL is not executed automatically in order to allow you to confirm whether those users and logins really can be dropped.

The script can check multiple SQL instances at once. Either by interrogating an asset database. My company uses Landesk. Alternatively, a list of instance names can be entered manually.

In order for the script to run successfully, the user must have permissions  and access to all the SQL Server instances listed and read permissions on the Active Directory server. You'll also need to have the Powershell Active Directory module and SQLPS installed on the client machine. To install the Active Directory module see:

UPDATE: Based on some feedback I've altered the script to use the Windows login's SID instead of name. This allows for any name changes. I've also added a Domain parameter so that if you have users from other trusted domains the script will only find the users from your preferred domain. By default the script uses the $env:USERDOMAIN environment variable. But the parameter can be changed as needed.


Anonymous said...

Tried it. It is pretty good. But, please make sure before running the Drop in SQL, I noticed that it picked up the SQL service account that the current instance running on.

Sasan Fakharpour said...

Looks great but I have a very odd issue..
the below line works fine if I use it in the console pane of the ISE and by itself if used in the script pane but it returns blank if used as a part of the script. I have called the file from PS command and ran the script in debug mode but to no avail. Any thoughts?
Get-ADUser -Server $ADServer -Filter {SamAccountName -eq "$user"} | select Name
PSVersion 5
Windows 10

Thomas Mucha said...

Sasan, you might want to copy the updated version. I've made a couple of changes that should resolve your problem.