Script to clean up “Windows” logins no longer in AD

11

March 1, 2013 by Kenneth Fisher

I was scanning http://dba.stackexchange.com and ran across the following question:

http://dba.stackexchange.com/questions/31478/sql-server-script-to-delete-accounts-no-longer-in-active-directory

Basically the OP wanted to know how to get rid of “Windows” or AD logins. Mike Fal answered with a very cool script I just had to blog about. Basically it scan’s through the Windows logins in sys.server_principals and uses xp_logininfo and a try catch operator to check if they exist or not and print a drop statement if they don’t. Here is the script if you don’t want to follow the link. I think I’ll change type=’U’ to type IN (‘U’,’G’) to check groups as well since we get a fair number of those also.

declare @user sysname
declare @domain varchar(100)

set @domain = 'foo'

declare recscan cursor for
select name from sys.server_principals
where type = 'U' and name like @domain+'%'

open recscan 
fetch next from recscan into @user

while @@fetch_status = 0
begin
    begin try
        exec xp_logininfo @user
    end try
    begin catch
        --Error on xproc because login doesn't exist
        print 'drop login '+convert(varchar,@user)
    end catch

    fetch next from recscan into @user
end

close recscan
deallocate recscan

11 thoughts on “Script to clean up “Windows” logins no longer in AD

  1. Perry Whittle says:

    I prefer this, this grabs a list of invalid domain logins. Add to it to drop the logins found

    IF (OBJECT_ID(‘tempdb..#invalidlogins’) IS NOT NULL)
    BEGIN
    DROP TABLE #invalidlogins
    END

    CREATE TABLE #invalidlogins(
    perr ACCTSID VARBINARY(85)
    , NTLOGIN SYSNAME)

    INSERT INTO #invalidlogins
    EXEC sys.sp_validatelogins

    SELECT NTLOGIN FROM #invalidlogins
    order by 1

    • Ok, so that is a much easier way to do it🙂 It’s amazing how many system stored procedures there are out there.

      • Robert Eder says:

        If the SQL Server service account is a local account, Xp_logininfo will return error 0x5, which means access denied, for a valid domain account. This results in every domain account listed to drop.

        The sp_validatelogins stored procedure will produce the same results whether the SQL Server service account is a local account or domain account.

      • Perry Whittle mentioned sp_validatelogins but I hadn’t realized the flaw in xp_logininfo. Good to know. Thanks!

  2. Marcel Miklovic says:

    select name from sys.server_principals
    where type = ‘U’ and name suser_sname(SUSER_SID(name))

  3. Another way of changing the login type check is to use a regex

    Instead of type IN (‘U’,’G’)

    Try
    LIKE ‘[UG]’

    Just a few characters shorter to type and works really well.

  4. near2goal says:

    Hi,
    I found your website with very informative and mature stuff. Thanks for sharing, I already added my email account in “follow me”.

    Actully I was trying to use the code and in my test environment I created on AD user and using it I created a login in SQL SERVER. Later I disabled and deleted the account from AD and executed the script (given in this post) but I didn’t have any result. Would you please advise if I am missing something or I am not in the scanario to get some resultset.

    Best Regards

    khalil

    • Read Robert Eder’s comment. It turns out that xp_logininfo has a bug (or different result) when it comes to local accounts. It may be you are running into something similar.

      I prefer Perry Whittle’s version (first comment) using sys.sp_validatelogins at this point.

      Thanks for the comment🙂

  5. Frank says:

    This is awesome. I have been struggle with this phase-out Windows logins for awhile!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,569 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: