January 7, 2003 at 12:48 pm
We have nightly jobs going on which refreshes databases, some from it's own backup and some from backup of other databases. I know that restoring DB from other DB usually takes user permission away from current Database.
But as I see it today, I saw users have permissions on some of the databases which were restored. i.e. user access ware not affected while in some database that were restored users lost permissions.
How can I make sure that every time restore runs user that had permission to Database, maintains it n exact same way.
Any help would be appreciated.
Thanks.
January 7, 2003 at 1:11 pm
NT logins will work, SQL logins may or may not - depends on whether the ID in sysxusers is the same on both servers. Quick fix is to run sp_change_users_login on the restored db to fix up, better is to fix sysxlogins. Look for an article in a week or two that will cover this in more detail. We have several articles and many thread about sp_change_users_login if you need help with it.
Andy
January 7, 2003 at 3:21 pm
In a situation like you are talking about I think I would create a table to hold the user login and role they belong to for each database. Then simply walk the table and do sp_dropuser, sp_adduser after restoring the db. I think the reason this happens is that the user sids get out of sync.
Gary Johnson
DBA
Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
January 7, 2003 at 3:32 pm
The sids do get out of sync, but add/drop is more work than you need to do. Take a look at sp_change_users_login code to see, it just fixes the sid.
Andy
January 8, 2003 at 2:34 pm
Thanks, Andy. I think sp_change_users_login is the way to go. Also instead of SQL users I created new users with NT authntication, and it seems to keep the windows users after Database is restored.
Thanks for your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply