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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
IF OBJECT_ID(N'tempdb..#Results', N'U') IS NOT NULL DROP TABLE #Results; CREATE TABLE #Results ( AcctName sysname NOT NULL , Name nvarchar(1000) NULL , IsDisabled bit NULL ); DECLARE @DomainName sysname = '[domain]'; --replace with your DOMAIN name. DECLARE @LDAPDC sysname = '[your_company_dc_goes_here]'; --replace with LDAP DC DECLARE @acctName sysname; DECLARE @cmd nvarchar(max); DECLARE @stmt nvarchar(1000); DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC FOR SELECT PrincipalName = ua.name , N'SELECT name, samAccountName, objectSid, userAccountControl FROM ''''LDAP://' + @LDAPDC + N''''' WHERE objectSID = ''''' + dbo.fn_SIDToString(ua.sid) + '''''' FROM sys.server_principals ua WHERE ua.type_desc = 'WINDOWS_LOGIN' AND ua.name LIKE @DomainName + N'\%' COLLATE SQL_Latin1_General_CP1_CI_AS GROUP BY ua.name, ua.sid ORDER BY ua.name; OPEN cur; FETCH NEXT FROM cur INTO @acctName, @stmt; WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = N' DECLARE @IsDisabled bit; DECLARE @AcctName sysname; DECLARE @name nvarchar(1000); SET @AcctName = ''' + @acctName + N'''; SELECT @IsDisabled = CASE WHEN ds.userAccountControl & 0x2 = 0x2 THEN 1 ELSE 0 END , @name = ds.name FROM OPENROWSET(''ADSDSOObject'', ''adsdatasource'', ''' + @stmt + N''') ds; SELECT AcctName = @AcctName, Name = @name, IsDisabled = @IsDisabled; '; INSERT INTO #Results EXEC sys.sp_executesql @cmd; FETCH NEXT FROM cur INTO @acctName, @stmt; END CLOSE cur; DEALLOCATE cur; SELECT * FROM #Results; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
-- function to translate binary format SID into Active Directory string-based-format CREATE FUNCTION [dbo].[fn_SIDToString] ( @BinSID AS varbinary(100) ) RETURNS varchar(100) AS BEGIN IF LEN(@BinSID) % 4 <> 0 RETURN(NULL); DECLARE @StringSID varchar(100); DECLARE @i AS int; DECLARE @j AS int; SET @StringSID = 'S-' + CONVERT(varchar(100), CONVERT(int, CONVERT(varbinary(100), SUBSTRING(@BinSID, 1, 1)))); SET @StringSID = @StringSID + '-' + CONVERT(varchar(100), CONVERT(int, CONVERT(varbinary(100), SUBSTRING(@BinSID, 3, 6)))); SET @j = 9; SET @i = LEN(@BinSID); WHILE @j < @i BEGIN DECLARE @val binary(4); SET @val = SUBSTRING(@BinSID, @j, 4); SET @StringSID = @StringSID + '-' + CONVERT(varchar(100), CONVERT(bigint, CONVERT(varbinary(100), REVERSE(CONVERT(varbinary(100), @val))))); SET @j = @j + 4; END RETURN @StringSID; END GO </code> |
The results look like this:
╔═════════════╦════════════════╦════════════╗ ║ AcctName ║ Name ║ IsDisabled ║ ╠═════════════╬════════════════╬════════════╣ ║ DOMAIN\user1 ║ Vernon, Hannah ║ 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.
I wrote something very similar a while back and the very first comment was someone pointing out this:
sys.sp_validatelogins
It's a lot easier 😀
— Kenneth Fisher (@_@) (@sqlstudent144) February 3, 2019
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.
Hi Hannah Disabled Active Directory Accounts in T-SQL not working for me. I have mention LDAP and Domain but not working> Please help me for the same
You’ll need to provide more detail about the actual problem. I suggest creating a question at the DBA Stack Exchange site. Also, please ensure you read about creating a minimal, complete, and verifiable example.
Thank you! Great little script.