SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Restore database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tocroi72
Yak Posting Veteran

89 Posts

Posted - 02/17/2006 :  11:56:00  Show Profile  Reply with Quote
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

United Kingdom
3608 Posts

Posted - 02/17/2006 :  12:06:01  Show Profile  Reply with Quote
Just run:

EXEC sp_change_users_login 'Auto_Fix', 'user'
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 02/17/2006 :  12:07:24  Show Profile  Reply with Quote
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


Go to Top of Page

tocroi72
Yak Posting Veteran

89 Posts

Posted - 02/17/2006 :  14:56:50  Show Profile  Reply with Quote
Thank you Both. Appreciated your quick response.
Go to Top of Page

schuhtl
Posting Yak Master

USA
102 Posts

Posted - 02/21/2006 :  09:00:29  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000