Infrastructure at your Service

Stéphane Haby

SQL Server tips: how to list orphaned logins

I read a lot of about orphaned database users in SQL Server, but I have almost never read about orphaned logins. Many of my customers migrate or remove databases in SQL Server. They forget – not every time but often – to remove the logins and jobs associated with these databases. I have created a script – without any cursors, YES, it is possible – allowing to search all logins who are not “attached” to a database of an instance.
In my example, I have created different logins to cover different scenarios

  • User01 is the database owner from AdventureWorks
  • User02 has db_datareader and db_datawriter for AdventureWorks
  • User03 has SELECT permission for all tables in the schema Person
  • User04 has the server role dbcreator
  • User05 has the VIEW ANY DATABASE server permission
  • User06 has nothing (it is my poor orphaned login!)

You now know that the only orphaned login I have is “User06″.

Step 1: Get all database-users

The first step is to gather all database-users from all databases of the instance. I use the view sysusers and the non-documented stored procedure sp_MSforeachdb. The only piece of information I need is the SID, but for a better understanding, I will display the name.

I have saved the result in a temporary table I will use it in my step 2:

orphanedlogins01.png
Remarks:

  1. I don’t exclude system databases from my example, to be sure not to delete a login which is linked to a system database.
  2. I don’t exclude dbo from my exception list (like sys, guest or INFORMATION_SCHEMA). Why? … Go to step 2…

Step 2: Comparison between logins and database-users

At this step, I will find logins which are not in my temporary database-users list. I use the EXCEPT command to do my selection and I don’t use a cursor.

I exclude all server roles, name that begin with ‘NT ‘ like ‘NT AUTHORITYSYSTEM, and name that begin with ‘##MS_’ and the sa.

orphanedlogins02.png

In my logins list, I onlyneed to  have User05 (I don’t deal with server level permission) and User06, but we realize that User01 is also in this list.

Why? It’s linked to my remark 2. My user01 is connected to the user dbo as the database owner. The real comparison must be made with the SID and not with the name.

Then my query is:

orphanedlogins03.png

You can only see 3 IDs and User01 is not in my list anymore.

Step 3: Select logins without server level permissions

In some cases, like for a monitoring login, you grant server level permissions. In order to have a complete script, I remove all logins with these permissions from my orphaned logins list.

I use server_permissions and server_principals views to get permission per logins.

orphanedlogins04.png

And Finally, I have found my User06 login.

Conclusion

This script is perhaps not perfect, but I hop, it can help you to cleanup your orphaned logins.
You can download the script here: dbi_orphaned_logins.zip

 

Leave a Reply

Stéphane Haby
Stéphane Haby

Delivery Manager