Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Login/User Corruption Expand / Collapse
Author
Message
Posted Thursday, December 13, 2012 11:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 2:44 PM
Points: 6, Visits: 24
It is common place for us to restore a backup when moving from a QA/Staging server to a Production server. When I do this, however, I sometimes have to drop by application's login and database user and run a script to re-create them. Sometimes, the Enforce Password Policy checkbox is checked after a backup/restore. What's the best way to make sure that I don't have to drop/re-create the login/user?
Post #1396335
Posted Thursday, December 13, 2012 12:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
Not corruption.

When the logins were created on the QA server they were not created with the same SID as the production server. Hence when the backup is restored, the SID in the database for the user does not match the SID of the login. It's called orphaned users.

Best way to fix it permanently is to drop all logins, script the logins from production with their SIDs and then run that script on QA. Once that's done, any backup from production that's restored will have the same SID for user and login and the logins will automatically work,



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1396341
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse