Script to clean up “Windows” logins no longer in AD
14March 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
Category: Microsoft SQL Server, Security, SQLServerPedia Syndication, T-SQL | Tags: code language, language sql, microsoft sql server, security, server permissions, T-SQL
14 thoughts on “Script to clean up “Windows” logins no longer in AD”
Leave a reply to Kenneth Fisher Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
SQL Studies 
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.
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!
select name from sys.server_principals
where type = ‘U’ and name suser_sname(SUSER_SID(name))
I’m not sure what you mean by this? Would you explain more?
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.
Hi,
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
khalil
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 🙂
This is awesome. I have been struggle with this phase-out Windows logins for awhile!
Glad it helps. Make sure you read the rest of the comments since there is some good stuff in there.
Simply brilliant and nice commentary, as well.
Thanks 🙂 Make sure you look in the comments. The best method was in there if I remember correctly.
Yes, I agree. For a script, I was working on I used sys.sp_validatelogins to make sure that the Account has yet to be deleted from Active Directory ( AD ).