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

7

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
About these ads

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

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

Follow

Get every new post delivered to your Inbox.

Join 428 other followers

%d bloggers like this: