Orphaned User

Orphaned Users

Definition: A user which is not mapped with any of the Logins is called Orphaned user.  

Information about the mapping of a SQL Server login to a database user is stored within the database. It includes the name of the database user and the SID of the corresponding SQL Server login. The permissions of this database user are used for authorization in the database.

A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance. A database user can become orphaned if the corresponding SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.

Note : A SQL Server login cannot access a database in which it lacks a corresponding database user unless guest is enabled in that database.

  We have our database "sales" on our integration environment. This database has user with name
"mylogin" and this server has login "mylogin" which is mapped with this user i.e. (user: "mylogin") --> (login:"mylogin"). Application is using this login ( specified in web.config file) to use Sales database.

  Following script will give  SId for login and user : 
  SELECT * FROM sys.sql_logins WHERE name = 'mylogin'
--sid :0xCBA915F7B9C23C4386E0918C13409323

SELECT * FROM sys.database_principals WHERE name = 'mylogin'
--sid :0xCBA915F7B9C23C4386E0918C13409323

Here both sid are same .

We got new backup of Sales database from production environment, we have to restore it on integration environment. After restore this on integration environment I try to use my application , Application giving exception, application is not able to access Sales database using "mylogin" user ID,
even we have both login and user with name "mylogin".

 I checked  the SID for login "mylogin"  and user "mylogin" using following script:


SELECT * FROM sys.sql_logins WHERE name = 'mylogin'
  --sid :0xCBA915F7B9C23C4386E0918C13409323

SELECT * FROM sys.database_principals WHERE name = 'mylogin'
--sid :0x260AA0D3041901478956EA9EFA377FC9
  Here we are getting different values for SID.

I ran the following script to shutout this problem:

Syntax: sp_change_users_login 'update_one' ,<login name >,<username> 
Example :
sp_change_users_login 'update_one' ,'mylogin','mylogin'



-- Now check once again after executing above query
SELECT * FROM sys.sql_logins WHERE name = 'mylogin'
--sid :0xCBA915F7B9C23C4386E0918C13409323


SELECT * FROM sys.database_principals WHERE name = 'mylogin'
--sid :0xCBA915F7B9C23C4386E0918C13409323
  Here we are getting same SID. Now I am able to connect with my database.

•   Using following system sp we can list all the orphan users.

               sp_change_users_login 'report'