Imagine that we own a large apartment building. And naturally, over time different tenants will come and go. But, what if we never bothered to change the keys to the apartments and we never attempted to get the keys back as a tenant leaves. Do you think this would pose a security risk? Well, undoubtedly so!
Similarly, when it comes to database servers, we should have a standard security process to check who has rights to a SQL Server (https://sqlbadboy.wordpress.com/2013/03/22/is-it-safe-is-it-secure/). And as people come and go from our organization, we should have a process to change certain passwords and cleanup the security on our servers. However, there are many companies that do not do this. And this becomes especially evident whenever you are starting a new job or taking over a new server.
In this post, I am NOT going to focus on changing passwords for domain and/or SQL Server logins. That should be straightforward. My focus for this post will be how to identify invalid Windows logins and orphaned users in databases.
*** PLEASE NOTE: If your standard is to use only domain groups as security principals, then you will generally not have the same type of mess to cleanup. The thoughts in this post are targeted for environments where domain user accounts and SQL Server logins are used. Also, I am discussing the situation where contained databases are NOT being used.
Identifying Invalid Windows Logins
The code below uses sp_validatelogins (https://msdn.microsoft.com/en-us/library/ms181728.aspx) to identify the Windows users and groups that no longer exist in the Windows environment, and then simply generates DROP LOGIN statements (https://msdn.microsoft.com/en-us/library/ms188012.aspx).
DECLARE @InvalidLogins TABLE ( SID VARBINARY(85) NOT NULL, LoginName NVARCHAR(128) NULL ) INSERT INTO @InvalidLogins EXEC sp_validatelogins SELECT LoginName, DropCmd = 'DROP LOGIN ' + QUOTENAME(LoginName) FROM @InvalidLogins
Identifying Orphaned Database Users
An orphaned database user is a user (database principal) that does NOT have a corresponding server login (server principal). I have listed code that uses two different sets of tables. One way uses the system tables, sysusers and syslogins, and the second way uses catalog views, database_principals and server_principals. Also, this code is database specific. So, if you want to find all the orphaned database users on the entire server, you will need to run this code in each database.
1) Using system tables
SELECT DatabaseName = DB_NAME(), UserName = u.name, DropCmd = 'DROP USER ' + QUOTENAME(u.name) FROM sys.sysusers u LEFT OUTER JOIN sys.syslogins l ON u.sid = l.sid WHERE l.sid IS NULL AND u.islogin = 1 AND u.hasdbaccess = 1 AND u.name NOT IN ('dbo', 'public', 'guest')
2) Using catalog views
SELECT DatabaseName = DB_NAME(), UserName = d.name, DropCmd = 'DROP USER ' + QUOTENAME(d.name) FROM sys.database_principals d LEFT OUTER JOIN sys.server_principals s ON d.sid = s.sid WHERE s.sid IS NULL AND d.authentication_type_desc IN('INSTANCE', 'WINDOWS')
So, the real takeaway from this post is to make sure that you have a security processes in place to keep your security clean.