Thursday, 2 August 2012

SQL Server:Finding the Orphaned Users

Orphaned user is a common problem in SQL server world.When I was working on internal security audit of our production servers, I realized that sp_change_users_login with report option will not work with users associated with the windows login.

Who is an Orphan Users?

An user in a database is called orphaned when the associated login does not exists in the server or login has different SID. The first scenario can happen when you delete a login from the server. For example you created a login for an employee Mydomain\Lastname.Firstname  and given access to couple of databases.Later this employee left the company or moved to different department.As a part of process we will delete his/her login (Mydomain\Lastname.Firstname) from the server. Now the databases on which he had access will have orphaned users.

The second scenario will happen when you restore the database from one environment (instance) to  another environment(instance) or when you drop the login and create it again.For example you have login Mydomain\Lastname.Firstname in two instances namely INST1 and INST2. This login has db_owner right on one of the database (MyDb) in INST1. Now you have taken backup of MyDb and restored it on the INST2 and surprisingly Mydomain\Lastname.Firstname will not be able to access the restored database. If he/she tries to access the database , SQL server throw  an error

Msg 916, Level 14, State 1, Line 1
The server principal "Mydomain\Lastname.Firstname" is not able to access the database "MyDb" under the current security context.

When you try to create a user on the restored database for Mydomain\Lastname.Firstname , it will throw an error as below
Msg 15023, Level 16, State 1, Line 1
User, group, or role ' Mydomain\Lastname.Firstname ' already exists in the current database.

This is happening because the SID of the login and user in the database is not matching. You can check this by examining the catalog views sys.server_principals and sys.database_principals.

Identifying the orphaned users

To keep the database environment clean and safe, it is important to  delete/fix the orphan users. In general ,the orphaned users created by the  first scenario(login deleted) should be deleted and orphaned users created by second scenario should be fixed. The easiest way to identify the orphaned users is , use the system procedure sp_change_users_login  with report option as given below.


But interestingly, this procedure will list only the orphaned users associated with the SQL login. If the login associated with windows login, the above procedure will not return the desired result. Let us see an example.



CREATE USER Testwindows FROM LOGIN [MyDomain\TestWindowsLogin]

DROP LOGIN [MyDomain\TestWindowsLogin]

Now the user Testwindows  in MyDb is an orphaned users as the login associated with it does not exists. Let us execute procedure sp_change_users_login to report the orphaned users on MyDb database.


This execution will not report the TestWindows user as orphaned user.  I have used the script List Orphaned Users.sql  to list the orphaned users from the all databases of an instance.In the script, I have used sp_change_users_login to list the orphaned users associated with SQL logins and custom scrip by joining sys.server_principals and sys.database_principals for orphaned users associated with windows logins.

Also note that in SQL server you can create users with out login for some special purpose. Be careful with that type users while looking for orphaned users  using the catalog view sys.server_principals and sys.database_principals. This special users can be identified by using the below query.

SELECT * FROM sys.database_principals WHERE (LEN(sid) > 16) AND TYPE IN ('S')

I will explain how to fix the orphaned users in my next post.

If you liked this post, do like my page on FaceBook 


  1. Wouldn't the second scenario listed above only happen for SQL Logins? If it is a Windows login the SID should be the same across the domain?

    1. Yes it will happen only for SQL login if the restoration is happening in same domain.


  2. Thanks for posting this useful content, Good to know about new things here, Let me share this, . LINUX training in pune


  3. I loved the way you discuss the topic great work thanks for the share, Let me share this, Hadoop training in pune

  4. شركة نقل عفش
    اهم شركات مكافحة حشرات بالخبر كذلك معرض اهم شركة مكافحة حشرات بالدمام والخبر والجبيل والخبر والاحساء والقطيف كذلك شركة رش حشرات بالدمام ومكافحة الحشرات بالخبر
    شركة مكافحة حشرات بالدمام
    شركة تنظيف خزانات بجدة الجوهرة من افضل شركات تنظيف الخزانات بجدة حيث ان تنظيف خزانات بجدة يحتاج الى مهارة فى كيفية غسيل وتنظيف الخزانات الكبيرة والصغيرة بجدة على ايدى متخصصين فى تنظيف الخزانات بجدة
    شركة تنظيف خزانات بجدة
    شركة كشف تسربات المياه بالدمام
    شركة نقل عفش واثاث

  5. شركة نقل عفش بالرياض وجدة والدمام والخبر والجبيل اولقطيف والاحساء والرياض وجدة ومكة المدينة المنورة والخرج والطائف وخميس مشيط وبجدة افضل شركة نقل عفش بجدة نعرضها مجموعة الفا لنقل العفش بمكة والخرج والقصيم والطائف وتبوك وخميس مشيط ونجران وجيزان وبريدة والمدينة المنورة وينبع افضل شركات نقل الاثاث بالجبيل والطائف وخميس مشيط وبريدة وعنيزو وابها ونجران المدينة وينبع تبوك والقصيم الخرج حفر الباطن والظهران
    شركة نقل عفش بجدة
    شركة نقل عفش بالمدينة المنورة
    شركة نقل اثاث بالرياض
    شركة نقل عفش بالدمام
    شركة نقل عفش بالطائف