Disabled Active Directory Accounts in T-SQL

SQL Server, by default, uses Windows Authentication to provide integrated Active Directory authentication to users. This makes it extremely easy to setup new users; you only need to know the Active Directory account name, or even better, the name of an Active Directory security group, to provide users with access to data stored in SQL Server. However, your network security department may be disabling accounts instead of deleting them, when an employee leaves the company, leaving unneeded disabled Active Directory accounts hanging around in SQL Server.

detect and disable active directory accounts

          Active Directory Integration. #Boring but #Indespensible

It’s easy to add users via Windows Authentication, but how do you know if those accounts you added three years ago are still necessary? Maybe your company policy is to remove ex-employees from database servers, instead of leaving disabled accounts in place.

The code below shows how to use T-SQL to detect domain accounts that have been disabled in Active Directory.

You’ll need to insert your domain name into the code above, at line 10. You’ll also need to know the LDAP DC for Active Directory where your users reside; in the example above, I indicate that with [your_company_dc_goes_here] on line 11 – that might need to look something like DC=head-office,DC=contoso,DC=com for the typical contoso.com head office example used by Microsoft.

The fn_SIDToString function code looks like:

The results look like this:

╔══════════════╦═════════════╦════════════╗
║  AcctName    ║    Name     ║ IsDisabled ║
╠══════════════╬═════════════╬════════════╣
║ DOMAIN\user1 ║ Vernon, Max ║    0       ║
║ DOMAIN\user2 ║ Smith, John ║    1       ║
╚══════════════╩═════════════╩════════════╝

Thanks to Kenneth Fisher for pointing me at the sys.sp_validatelogins system stored procedure that can be used to provide a list of Windows logins where the associated account no longer exists.

That’s a nice way to get a quick list of accounts that have been deleted from the domain. The code shown in my post above provides a nice way to get a list of logins that have been disabled, which is complementary to the system stored procedure.

Let me know if you found this post helpful!

This post is part of our continuing series on SQL Server Security.