Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

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

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

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...