LearnItFirst User Forum

 SQL Server 2008 DBA Training Videos
Welcome Guest Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » SQL Server Database Administration » SQL Server Security » Backup and Restore (It's affecting my users) PLEASE HELP!!!
Backup and Restore (It's affecting my users) PLEASE HELP!!! Options
Previous Topic · Next Topic
Jason Love
Posted: Monday, July 16, 2007 1:08:36 PM
Rank: Newbie

Joined: 7/16/2007
Posts: 1
Points: 3
Where do you live?: Houston
 There is a process in place where we constantly backup database 1 daily and restore the full backup to database 2 (which is the reporting server).

Database 1 is real time replication with Sybase and the report viewers/suits want static data during the day.

An issue that occurs is once the database on server 2 is restored, the user permissions are not linked with the initial logins that were in place from the previous day.

Is there anything I can do to resolve this, with out pushing a script daily?

Can I set the database to public or something like that or create a server role? The easiest way is to make everyone SA, but I am not crazy!!!

Jason

Go Dynamo!
Back to top
 
Scott Whigham
Posted: Thursday, July 19, 2007 12:01:44 PM


Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX
 Jason - thought I had already answered this... Sorry...

If I understand the problem correctly, your problem is caused because the login SIDs on server1 are different from the login SIDs on server2. Years ago, some DBA probably learned about using sp_change_users_login and the "magical" AUTO_FIX parameter and has a script that runs often - am I right?

In any case, that's not really what you want here. What you want is to have the logins on both servers to have the exact same SIDs. If they have the same SIDs, then when you backup from Server1 and restore to Server2, everything will be in sync. This SQL Server script from MS PSS is what you need: sp_help_revlogin - http://support.microsoft.com/kb/246133

Back to top
Web  
Users browsing this topic
Guest


Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

© Copyright 2004-2009 LearnItFirst.com LLC. All rights reserved. All trademarks remain the property of their respective owners. This site is not affiliated in any way with the Microsoft Corporation.