Restore database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Restore database

Author

Topic

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2006-02-17 : 11:56:00

Hello all,
I have 2 servers- one development and one production - they both have the same database A and same SQL server login BB for this database. I wanted to refresh database in DEV by doing the restore a backup from Production. The restoration complete successfully . HOwever the SQL server login BB doesn't work in database A in DEV anymore, i have to drop user BB from database in DEV and give it permission again . Do i have to do it everytime I refresh my database in DEV from production or there're something i just need to do after i restore the database so i don't need to drop the user.?

Thank you for your help.
Tocroi

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-17 : 12:06:01

Just run:

EXEC sp_change_users_login 'Auto_Fix', 'user'

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-02-17 : 12:07:24

Vyas has a good article on why this happens.

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

One workaround is to use the sp_help_revlogin stored procedure to create the same users (including their SIDs) in your DEV environment that exist in your PROD environment. Syncing the SIDs will eliminate this orphaned user issue completely.

Keep in mind that sp_help_revlogin will also bring over their passwords in encrypted form. This means that the users passwords will be the same in DEV and PROD. This might not be a problem, but I prefer to have different passwords in use across a DEV/TEST/PROD landscape.

-ec

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2006-02-17 : 14:56:50

Thank you Both. Appreciated your quick response.

schuhtl
Posting Yak Master

102 Posts

Posted - 2006-02-21 : 09:00:29

I can't remember where I found this on this site but here is a simple script that works great! I think Tara wrote it....

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources