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:
- I don’t exclude system databases from my example, to be sure not to delete a login which is linked to a system database.
- 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.
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:
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.
And Finally, I have found my User06 login.
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