October 14, 2004 at 7:05 am
I have a simple backup job who is taking a backup from a production database to disk every night, when the backup completes the next step is a restore to a testdabase. I have one login made under security/logins to the testdatabase and on the tab "database access" I have found the testdatabase and put a click in "permit" and put a click i "public" and "db_owner".
The problem is that when the restore is finished the clicks in logins to the testdatabase is gone and nobody can access the database before I have set the clicks again. WHY are the missing everytime ? WHAT can I do so I dont have to fix this every day ?
October 14, 2004 at 10:00 am
The reason the testdatabase users are missing after the restore is they aren't in the production database that is being backed up and restored. When you restore a database, even with another name, it restores everything that was in the production database including the users.
You could add a step to your job that adds the user and assigns it to roles in testdatabase. Put the step after the restore step.
Greg
Greg
October 14, 2004 at 10:29 am
Or "fix" the existing user in the prod database with the test login by using SP_CHANGE_USERS_LOGIN.
October 15, 2004 at 12:57 am
Thank you for the answers. If I just add the testuser login to the proddatabase together with the prodlogin then it should work right ?
October 15, 2004 at 7:32 am
If they are on the same box it should work, but if they are on different boxes you will have to use sp_change_users_login to sync the accounts.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
October 15, 2004 at 12:33 pm
Be careful about adding a test user to a production database though. Should the test user(s) have the same rights in production that they have in test? If not, then you should look at scripting the permissions and adding that to your restore job as Greg suggested.
Steve
October 15, 2004 at 12:40 pm
I agree with Steve. I am doing the same thing for our accounting department and have scripts adding/removing accounts after the restore.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
October 15, 2004 at 2:54 pm
Okay, I will do what the experts are sugesting and try Steve's solution. Thank you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply