Login vs. Userid

When I restore a database from the production environment to the
test environment without restoring master, I break the link
between master..syslogins and each userid in the databases sysusers table. (I cannot restore master as multiple instances of unrelated databases exist on the dataserver)

Can I update the userid's SID entry in the sysusers table to match
what is currently listed for the related login in master's syslogins table,
or are there other columns that are used to create the link between the
userid and login id?You can try update sids but my advice - recreate all logins with sids from your production server:

sp_addlogin [ @.loginame = ] 'login'
[ , [ @.passwd = ] 'password' ]
[ , [ @.defdb = ] 'database' ]
[ , [ @.deflanguage = ] 'language' ]
[ , [ @.sid = ] sid ]
[ , [ @.encryptopt = ] 'encryption_option' ]|||Is it to be assumed that your solution is counting on the Login being
exclusive to the userid being restored?|||This page will help

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q246133|||Create all logins and than restore dbs.|||snail: have to reference rhigdon's link and disagree with you, because this would have worked in 6.5, not in 7.0/2K case, because userid is referencing the SID, not the name field in syslogins.|||During creating logins it needs to set SIDs from production server...
See my first post.

Originally posted by rdjabarov
snail: have to reference rhigdon's link and disagree with you, because this would have worked in 6.5, not in 7.0/2K case, because userid is referencing the SID, not the name field in syslogins.|||rocket39,

I have a TSQL script for realligning user ids at my home. I'll post it for you this evening.

blindman|||I think rhigdon's link contains all the necessary scripts to do just that.|||There is a slight difference, in that my code realligns existing user logins for a given database to match those for the server.

Not as extensive as the code in the link, but usefull if you can't create a particular user under the same ID as another server because the ID is being used, or if there are objects that belong to the user and you don't want to have to drop and recreate them before synchronizing IDs.

blindman|||There is also sp_change_users_login to do sid alignments.|||Those Microsoft bastards stole my code!

Know any good lawyers?

blindman