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.

  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

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

Join 1,580 other followers

Follow me on Twitter

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