Doug Krawec (12/18/2007)
sunjiulu,Thanks for the explanation.
Yes, I am using SQL Server login rather than Windows Authentication. We have created a SQL Login of the form "ApplicationUser" with access to the single application database. The database I'm restoring is a copy of Production which is being used on a development box.
Is there a standard script that can be used to reset this SID on the restored database to match that of the instance of the SQL Server your are restoring to? I would assume you would need to check for the existence of "ApplicationUser".
doug,
the following sql is what I do in sql2000, I believe sql2005 has a system procedure to do it, maybe a google search will give you the name of the proc. here you go.
/*restore the database*/
restore database yourdbname from disk='d:\dropit\backup.BAK'
...
/*allow update system tables*/
sp_configure 'allow update', 1
reconfigure with override
/*get the sid*/
select sid,name from syslogins where name='yourloginname'
/*update system table to match the sid*/
update yourdbname..sysusers set sid=0x91729E827E48874A8883B8E08A9426F8 where name='yourloginname'
/*not allow update system tables*/
sp_configure 'allow update', 0
reconfigure with override