Quick question for everyone this week: how do you resynch your users after a database restore?
For same server restores you do not have this issue, but when restoring from prod to test, for example, you will find that the sid’s do not match and thus you either need to resynch the users or to drop/add.
Our solution right now is to capture the syslogins every night using OpsMgr and pump the data to a warehouse. We also do a sp_helplogins on each login and pump that information to our warehouse. So, if our objects are being given grants to database roles as opposed to users (and the roles have the same names in all environments), then our resynch is as easy as pressing a button.
With SQL2008 out as well as the use of Powershell these days I thought it might be time to revisit how resynchs are being done. I am thinking ours needs some updating.
Tom, I always take a little extra time to just add them to the server with the same SID so I don’t have to worry about it on later restores.
andy, do you (and others who responded) mean to imply that you have the exact same logins with the exact same names on your servers? otherwise i am not seeing how you would be keeping the SID’s in line, perhaps i am missing something and you are manually setting the SID?
here we have different logins for each environment, which is why we started down the path many years ago of capturing the syslogins nightly in order to do the resynch after a restore between prod and test. i am not sure it ever occurred to me to force the SID’s to match, mostly because the login names are different.
sp_help_revlogin modified to work with whatever version of sql server I’m working with.
If you’re in SSIS, you can pipe the output script from that to a variable then run it using an execute sql task.
Variably you can just take the generated script and run it manually on the server you’re restoring to as well if it’s an ad-hoc process or emergency restore to another machine.
On environments I’ve managed in the past I’ve had the proc run once a week and save the script off as part of my backup routine.
sp_help_revlogin takes care of SIDs and everything.
I also use sp_help_revlogin and then another procedure to fix the orphanes. I capture the message printed out from sp_help_revlogin in SSIS, write this to a .sql on the secondary servers and then execute the file. The reason I write it to a file is retention and added DR thoughts but the variable idea is also good.
Sounds like Brett and I have pretty much the same lines of processes going.
Yup — sp_help_revlogin seems like one of the best kept secrets for this issue. Works like magic, but relatively few people know about it.
I have a script based off of sp_change_users_login and sp_help_revlogin. Am on road for next week, but think if you go out to mssqltips.com and search on my name + orphan you’ll find it.
I use a Powershell script to recreate the users first and then another script to add the user to its roles.
Every night I generate the scripts to re-create the users, roles, etc, so I have them ready to use in situations like this.
Ditto here, sp_help_revlogin is my tool of choice for keeping the users, their passwords, and sids in sync. It can be found at http://support.microsoft.com/kb/918992.
I do the same thing as Tim and have a procedure that (eegads!) cursors through the users and runs the sp_change_users_login against them. I keep copies of the output of help_revlogin as well.
Tom,
I am behind on blog reading as you can probably tell, but I work just like Andy Warren in this case. We use the same login names for our Production, Development and Test environments. When I create a login in production, I immediately script that login out with the SID and use the scripts to create the login on the other servers. Then I change the password on the login and make any needed changes to that environment. This way when you restore the databases the users map to the logins properly.
yeah, that would not be allowed in our shop. different logins for each environment.