Simulation of Orphan Users and the fix !

A) EXECUTE THESE ON SERVER A

create database test1

use test1

create table table1
(
id bigint
)

declare @i bigint
set @i=1
while(@i<=100)
begin
insert into table1
values(@i)
set @i=@i+1
end

select * from table1

sp_help sp_addlogin
sp_addlogin @loginame='sqlcat1',@passwd='sqlcat1',@defdb='master',@deflanguage='us_english'
sp_addlogin @loginame='sqlcat2',@passwd='sqlcat2',@defdb='master',@deflanguage='us_english'
sp_addlogin @loginame='sqlcat3',@passwd='sqlcat3',@defdb='master',@deflanguage='us_english'
sp_help sp_grantlogin
sp_grantlogin @loginame='asiapac\smadhavan7'


sp_help sp_adduser
sp_help sp_grantdbaccess
sp_helprole
sp_help sp_addrolemember

sp_adduser @loginame='sqlcat1',@name_in_db='sqlcat1',@grpname='public'
sp_adduser @loginame='sqlcat3',@name_in_db='sqlcat3',@grpname='db_owner'

sp_grantdbaccess @loginame='sqlcat2',@name_in_db='sqlcat2'
sp_addrolemember @rolename='db_owner',@membername='sqlcat2'

--using adduser or grantdbaccess+addrolemember are same

select * from sysusers

sp_helpuser

backup database test1
to disk='C:\backups\full_test1.bak'

sp_helpdb 'test1'

-------------------------------------------------
-------------------------------------------------


B) EXECUTE THESE ON SERVER B

restore database test22
from disk='C:\backups\full_test1.bak'
with file=1,
move 'test1' to 'C:\backups\test_rest1.mdf',
move 'test1_log' to 'C:\backups\test_rest2.ldf'

sp_change_users_login 'report'
The output would be as follows :
sqlcat1 0x1770DFE18F9CD64A812C27DD61CC03C8
sqlcat2 0x1724891474E45A4599453E8F0A3C523F
sqlcat3 0xB884905510F3C0438BDFDE1D888DD612

Now execute : sp_change_users_login 'auto_fix','sqlcat1',NULL,'SQLcat123!'

When u specify null then it means user doesnt have a login to be linked to.
after the script is executed,for the user 'sqlcat1' a new login would be created with the same name as 'sqlcat1' and then the password given would be set to that login.

dbo db_owner NULL NULL dbo 1 0x0105000000000005150000008ACE31E646417D21BAEBAA568B860200
guest public NULL NULL guest 2 0x00
INFORMATION_SCHEMA public NULL NULL NULL 3 NULL
sqlcat1 public sqlcat1 test22 sqlcat1 5 0xF1218C686757F34CA65683218B31060F
sqlcat2 db_owner NULL NULL sqlcat2 6 0x1724891474E45A4599453E8F0A3C523F
sqlcat3 db_owner NULL NULL sqlcat3 7 0xB884905510F3C0438BDFDE1D888DD612
sys public NULL NULL NULL 4 NULL


watch the sqlcat1 it has now a login associated with it.

now create a new login for the server instance B as follows :
create login catsql1
with password='CATsql123!'

sp_change_users_login 'update_one','sqlcat2','catsql1'

dbo db_owner NULL NULL dbo 1 0x0105000000000005150000008ACE31E646417D21BAEBAA568B860200
guest public NULL NULL guest 2 0x00
INFORMATION_SCHEMA public NULL NULL NULL 3 NULL
sqlcat1 public sqlcat1 test22 sqlcat1 5 0xF1218C686757F34CA65683218B31060F
sqlcat2 db_owner catsql1 master sqlcat2 6 0xCE4CEA864162EA469F5FBB55FEEBC0E4
sqlcat3 db_owner NULL NULL sqlcat3 7 0xB884905510F3C0438BDFDE1D888DD612
sys public NULL NULL NULL 4 NULL

Have a look at the above query result.Watch for sqlcat2 it has now got the sql login associated with it and its nothing but catsql1.