SYSK 200: How To Detect and Remove No Longer Valid Windows Logins From SQL Server

  • Article

It is a good idea to occasionally audit your SQL servers for users/groups that no longer exist in the Windows domain, but still exist on the SQL Server.

This can happen, for example, when employees or contractors leave the company, or change their login name (marriage comes to mind), etc.  I’ve seen some companies create group names for a certain application, and a few months/years later delete them.  You get the point…

In this case, you may have logins on the SQL Server that have been orphaned.

As a security good practice, consider do the following:

1.      Do not allow user-ownership of objects.  Database objects should be owned by either DBO or by a schema.
2.      Do not define SQL Agent jobs with a domain account as the job owner.  Instead, define the job owner as “sa”.
3.      Avoid using LOCAL NT groups, since this can complicate permissions in the event of a server failover or a node failover in a SQL Server cluster.
4.      Use only windows domain groups, as SQL Server logins (which are less likely to change than user accounts), and add user accounts to the groups.  Then, add that group to a user-defined role inside the database.

If you do have a case where you end up with orphaned Windows logins, you can use the following script to detect and remove these orphaned logins.

NOTE:  If these logins have been added directly as users inside the database, they will need to be first dropped from the database.  In addition, if the user owns any objects inside the database, you will first need to assign ownership of the object to another user before dropping them.

create table #logins (sid varchar(128), NtLogin varchar(128))

INSERT #logins
EXEC sp_validatelogins

declare @login varchar(128)
declare @SQL nvarchar(500)

SET @Login=(SELECT MIN(NTLogin) FROM #logins)

WHILE @Login IS NOT NULL
BEGIN
--NOTE: FOR SQL 2005, use DROP LOGIN instead.
SET @SQL='exec sp_revokelogin '+QUOTENAME(@Login)

exec sp_executeSQL @SQL
SET @Login=(SELECT MIN(NTLogin) FROM #logins WHERE NTLogin>@Login )

END

DROP TABLE #logins

--VERIFY...
EXEC sp_validatelogins

Special thanks to Dan Carollo for this information!