Security Cleanup … Identifying Invalid Logins and Orphaned Users

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.

Advertisements
This entry was posted in Security and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s