Lost user after restore 2

[Nowell]

Hi,

On a nightly basis I restore a database called 'tmg2', from a backup set called 'tmg'. But everytime i do it I lose a user, the user still shows up in the database but you cannot log on using that user. I then have to delete the user and re-create it for the user to be able to log on again.

The syntax I using is as follows:


RESTORE DATABASE TMG2
FROM DISK = 'D:\data\TMG_DUMP_PM.BAK'
WITH MOVE 'TMG_Dat' TO 'D:\Data\TMG2.MDF' , MOVE 'TMG_Log' TO 'E:\TMG_Log2.LDF'
, REPLACE


Has anyone had this kind of prob before?

 

[nigelrivett]

Are they on the same server?
It's probably becaue the user on the source server has a different logon ID to that on the destination so the mapping is lost. Although the user is in the database it doesn't map to the correct login.

Have a look at sysusers and syslogins

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Nowell]

Hi,

Yeah they are on different servers.
I have had a look at those tables but in sysusers they both have the same UID.

How do i fix this?

 

[Corran007]

EXEC sp_change_users_login 'Auto_Fix',NAME,NULL
loop it up in bol if you have more quetions

 

[Nowell]

Hi,

But when I back up and restore tonight I will have the same problem again. I want to know how I can fix this permenantly.

Thanks

 

[nigelrivett]

Are the sid's the same in master..syslogins? That's what will map the user to the login

see

http://vyaskn.tripod.com/troubleshooting_orphan_users.htm

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Nowell]

Hi,

The sid is a binary field how do I view the sid so I can change it to the value on the source server.

Thanks for the assistance so far guys

 

[Corran007]

I know of no way to permantly fix it. Just have that command set up to run right after the restore. either as an additional step int he job or as a new job.

 

[Nowell]

Thanks chaps