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.

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:

-- 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

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.