June 9, 2011 at 12:50 pm
Hi,
Problem: Restore a db on dev and qa from prod db backup without wipingout permissions. But restored without noting the permissions. All the permissions got wipedout. The intial dev db backup got replaced overnight with the restored dev db backup.
Question: Is there any way to get the original dev db backup and restore the permissions.
Need help ASAP!
Thanks
SVR
June 9, 2011 at 1:37 pm
SVR (6/9/2011)
Hi,Problem: Restore a db on dev and qa from prod db backup without wipingout permissions. But restored without noting the permissions. All the permissions got wipedout. The intial dev db backup got replaced overnight with the restored dev db backup.
Question: Is there any way to get the original dev db backup and restore the permissions.
Need help ASAP!
Thanks
SVR
Database Users are hooked to a server Login's SID (security Identifier). SIDS are unique to the server. Well, simply speaking. So when you restore the database, the Database User, and their permissions are still in tact, but no longer match a valid Login on the new server. Look in BOL for the code to associate the user to a login on the new server. This will need doing after each time you restore.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
June 9, 2011 at 1:44 pm
Jim Murphy (6/9/2011)
Database Users are hooked to a server Login's SID (security Identifier). SIDS are unique to the server.
Not really... They won't match if auto-created, but they're not unique to a server.
You can create the logins on the second server and specify the SID (there are scripts that will generate the create logins from an existing login and specify the SID) and then when you restore the DB the login and user will automatically link up.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2011 at 2:14 pm
GilaMonster (6/9/2011)
Not really... They won't match if auto-created, but they're not unique to a server.You can create the logins on the second server and specify the SID (there are scripts that will generate the create logins from an existing login and specify the SID) and then when you restore the DB the login and user will automatically link up.
Yes. Thanks Gail. What I was trying to convey was if a Login named AppUser is created on one server, and an AppUser login is created on another server, they LIKELY won't have the same SID; just like in Windows, where any accounts created after the Default Administrator account will have an incrementing SID and it would be rare for two computers to have the same account name with matching SIDs. So for practical purposes the SID for this poster's Login is 99.x% likely to be different on his other computer.
That said, you are technically absolutely correct that they are not server unique.
I like what you said about a script to create a login with a given SID to match the other computer (if another login on server2 isn't already using that SID), so the users won't need to be constantly remapped to the auto created login after each restore. Cool.
Orig poster- also be aware that Logins can have Server Roles and it is assumed that both logins (one on each server) have matching server level permissions (or that you are aware of this and decided this is not a problem in your environment).
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
June 10, 2011 at 2:46 am
Here's a link to the scripts, with instructions, that MS provide for moving logins with their SIDs and passwords: http://support.microsoft.com/kb/918992
June 10, 2011 at 3:36 am
Jim Murphy (6/9/2011)
Yes. Thanks Gail. What I was trying to convey was if a Login named AppUser is created on one server, and an AppUser login is created on another server, they LIKELY won't have the same SID; just like in Windows, where any accounts created after the Default Administrator account will have an incrementing SID and it would be rare for two computers to have the same account name with matching SIDs. So for practical purposes the SID for this poster's Login is 99.x% likely to be different on his other computer.
Oh yes, absolutely. For SQL logins anyway. Windows logins take their SID from AD, hence should be the same.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2011 at 7:13 am
sounds to me like the user permissions in dev and qa (and users themselves)were different to the prod users so just remapping the logins wont get back the original database level permissions, those are lost.
Unless the older backups are held off server somewhere you have a problem.
You can try mapping the dev and qa logins to prod equivalents if there is equivalence and just use the permissions they have and take it from there adding permissions as users highlight errors. You would expect permissions to be the same with perhaps extra database roles so devs can test changes.
you need to script out the permissions required on the dev and qa databases and build this into the restore process when overwriting from production. There should be plenty on the web and this site itself that do that.
I have one that works in all versions but it is SQL2000 based
---------------------------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply