March 1, 2013 by Kenneth Fisher
I was scanning http://dba.stackexchange.com 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 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