There are three possible ways to make a user orphaned

• Explicitly drop the login that user belongs to
• Restore database on another instance of sql server.
• Restore of an old master backup

This article describes how to troubleshoot such kind of problems and details why it appears.

Creating orphaned users

Whenever you create a sql login or grand a windows group or login permissions to sql server instance a new record is inserted into syslogins (for sql server 2000)  or sys.server_principals - for sql server versions starting from SQL Server 2005. These tables are located in the master database. By the way sql server 2005-2008 also has syslogins view for backward compatibility but in next versions it will be retired.

Every sql server login gets its own Security ID assigned. It is SID filed in syslogins and sys.server_principals table of varbinary(85) type. The structure of this field is out of scopes of this article but you need to know that SID can be either auto generated of explicitly specified during login creation i.e.

Exec sp_addlogin @loginame = 'testlogin'

    ,@passwd = 'pass2'

    ,@sid = 0x4A0F0AD9A3901D4EB7AE45849D30A8D5

or

create login testlogin1 with password = 'pass2' , sid = 0x4A0F0AD9A3901D4EB7AE45849D30A8D1

 This optional param is very useful if we transfer logins and databases from one sql server instance to another.

Now having logins we create users in the database. This may be done using either sp_grandbaccess (for sql server 2000) or create user statement (sql server 2005 and sql server 2008). You must be in the database you want to grant permissions to when running these scripts

create database testusers

go

use testusers

GO

In other words permissions are given to the database they are given from.

exec sp_grantdbaccess 'testlogin'

create user testlogin1 for login testlogin1

Now lets select in the database testusers table sysusers (for sql server 2000 version) or sys.database_principals (sql server 2005-2008)

select * from sysusers

-- or for sql server 2005 and above only

select * from sys.database_principals

  Both tables return sid. Notice that these security IDs are exactly the same as we specified during login creation. Security ID is what database user and sql server login are connected by. Obviously there is no Foreign Key or other type of constraints for these values.

Now let's try to drop the logins we created before.

exec sp_droplogin testlogin -- this code workks in any version of sql server(sql server 2000, 2005, 2008)

drop login testlogin1 -- this work fine starting from sql server 2005

 And see what happened to our database users

 select * from sysusers

There are still users with SID field pointing to inexistent logins. This users became orphaned.

Reporting orphaned users

To see all orphaned users in the database use the following query

exec sp_change_users_login 'report'

 In our case it returns two of our users.

Now lets create the logins again without specifing its SID values letting sql server to assign those.

exec sp_addlogin @loginame = 'testlogin'

    ,@passwd = 'pass2'       

create login testlogin1 with password = 'pass2'

Now we recreated the logins and everything should be ok but running script

exec sp_change_users_login 'report'

we still get two orphaned users. That's because sql server genereted security IDs differrent that we specified (checkout ssylogins or sys.server_principals in master db).

 Fixing orphaned users

 To fix these orphaned users we need to relink security id of the user. Now it points to inexistent login and we need to point it to current security id of the login with the same name. For this purpuse we still use sp_change_users_login but with different parameters

EXEC sp_change_users_login 'update_one', 'testlogin1', 'testlogin1'

EXEC sp_change_users_login 'update_one', 'testlogin', 'testlogin'

 Thus we reestablished connection between database users and server principals and to check let's run

exec sp_change_users_login 'report'

It returns 0 records so the problem with orphaned users has been fixed.