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
