Troubleshooting Orphaned Users

When restoring a database backup to another server, you may experience a problem with orphaned users. This scenario displays and resolves the problem:

1. Alias the login janetl to dbo by executing sp_addlogin.

   sp_addlogin 'janetl', 'dbo'

     

2. Back up a database. In this example, back up Northwind.

   BACKUP DATABASE Northwind

   TO DISK = 'c:\mssql7\backup\northwnd'

     

3. Drop the database that was just backed up.

   DROP DATABASE Northwind

     

4. Drop the login.

   sp_droplogin 'janetl'

     

5. Restore the backed up database.

   RESTORE DATABASE Northwind

   FROM DISK = 'c:\mssql7\backup\northwnd'

     

   The janetl login is not allowed into the Northwind database unless the guest login is allowed. Even though the janetl login has been deleted, it still shows up (as an orphaned row) in the sysusers table:

   USE Northwind

   SELECT *

   FROM sysusers

   WHERE name = 'janetl'

     

To resolve orphaned users

1. Add a temporary login using sp_addlogin. Specify the security identifier (SID) (from sysusers) for the orphaned user.

   sp_addlogin @loginame = 'nancyd',

   @sid = 0x32C864A70427D211B4DD00104B9E8A00

     

2. Drop the temporary alias that belongs to the aliased SID using sp_dropalias.

   sp_dropalias 'nancyd'

     

3. Drop the original user (now orphaned) using sp_dropuser.

   sp_dropuser 'janetl'

     

4. Drop the original login using sp_droplogin.

   sp_droplogin 'nancyd'

     

See Also