When we backup a database from Server A, and then restore it to Server B, we bring a lot of database users from Server A to the server B. These users may or may not have the server level logins with the same names on server B. But even there are logins with the same name, the SID (Security ID) are still different. The result is that you can login by user name abc, but this login abc can not access the database she/he used to be able to. Why ? Because the login user abc and the DB user abc are different.

This problem is called Orphaned Users.

To detect if there are orphaned db users for a certain database, below is the script to run:

use mydatabase

exec sp_change_users_login ’report’

This script will report how many users are orphaned. Normally if you see an empty list. There is no orphaned users in this DB and you don’t need to worry about the orphaned problem.

What if you see some items there ?

Below is the script to resolve this problem:

DECLARE @username varchar(25)

DECLARE fixusers CURSOR
FOR

SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name

OPEN fixusers

FETCH NEXT FROM fixusers
INTO @username

WHILE @@FETCH_STATUS = 0
BEGIN
IF @username=’dbo’
BEGIN
EXEC sp_changedbowner ’sa’
END
ELSE
BEGIN
EXEC sp_change_users_login ’update_one’, @username, @username
END
FETCH NEXT FROM fixusers
INTO @username
END


CLOSE fixusers
DEALLOCATE fixusers

Here is another one which can achieve the same:

SET QUOTED_IDENTIFIER OFF
GO

DECLARE @SQL varchar(100)

DECLARE curSQL CURSOR FOR
select "exec sp_change_users_login ’AUTO_FIX’,’" + name + "’"
from sysusers
where issqluser = 1 and name NOT IN (’dbo’, ’guest’)

OPEN curSQL

FETCH curSQL into @SQL

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@SQL)
FETCH curSQL into @SQL
END

CLOSE curSQL
DEALLOCATE curSQL

go