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


March 1, 2013 by Kenneth Fisher

I was scanning and ran across the following question:

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

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)
    DROP TABLE #invalidlogins

    CREATE TABLE #invalidlogins(

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

    LIKE ‘[UG]’

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

  4. near2goal says:

    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


    • 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: Logo

You are commenting using your 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


Get every new post delivered to your Inbox.

Join 847 other followers

%d bloggers like this: