Script to clean up “Windows” logins no longer in AD
7March 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
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 0×5, 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.