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:\mssql\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:\mssql\backup\northwnd'
   

   The janetl login cannot access 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

Backing Up and Restoring Databases

BACKUP

RESTORE

sp_addlogin

sp_dropalias

sp_droplogin

sp_dropuser