September 24, 2012 at 8:29 am
We restore Production backups to our UAT server nightly to keep data refreshed. The devs are given access through a local server group. When the restores complete, I need to go in and recreate the user mapping for the group for the databases that were restored. Is there any way to get around this, or any way to automate this step? I am hesitant to update system tables (i.e. updating the SID) manually. Specifics:
SQL Server 2008 R2
WIndows Server 2008 R2
September 24, 2012 at 8:38 am
Sounds like you need to create the user based on the login then assign the nessesary roles to the user
Add something like the below to your script
CREATE USER [Domain\GroupName] FOR LOGIN [Domain\GroupName]
GO
EXEC sp_addrolemember N'db_owner', N'Domain\GroupName'
GO
Repeat the addrolemember for the different roles you want to grant the login
September 24, 2012 at 8:52 am
Jason Marshall (9/24/2012)
We restore Production backups to our UAT server nightly to keep data refreshed. The devs are given access through a local server group. When the restores complete, I need to go in and recreate the user mapping for the group for the databases that were restored. Is there any way to get around this, or any way to automate this step? I am hesitant to update system tables (i.e. updating the SID) manually. Specifics:SQL Server 2008 R2
WIndows Server 2008 R2
There is a local group on the primary server and a local group on the secondary server both with the same name, is that correct?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 24, 2012 at 8:54 am
The names are different, but the groups serve the same purpose.
September 25, 2012 at 1:24 pm
The SID for the two groups, no matter if they have identical names, will be different.
The database "user" SID for the group (as seen in sys.database_principals) needs to be updated to use the SID for the local group on the other server.
This can be done by executing ALTER USER <db_user> WITH LOGIN=<login_name>
September 25, 2012 at 1:28 pm
Nils, are you saying I need to update the SID on the 'restored' server with the SID of the 'restoring' server?
September 26, 2012 at 1:16 am
Is there any reason why the prod and UAT boxes can't be in a single domain or at least the same forrest, what you are trying to do is much simpler and secure using AD Groups
September 26, 2012 at 2:59 am
Let me try to explain a little bit better:
Product server=SERV1
UAC Server=SERV2
On SERV1 you have a local windows group called MyDevs. Lets say this group has SID 0x00001.
The group is registered as a login on SQL Server. In sys.server_principals you will find this group with the same SID as the local Windows group.
The login as granted permissions to a database, and a database user is created for the group. If you query sys.database_principals (in the correct database), you'll find a user with the same SID as the login and the Windows group. It's the SID that is used to map from a database user to a login and finally to a Windows/AD user/group.
On SERV2 you also have a local Windows group for your developers. The problem is that this group has a different SID than on SERV2. Let's say the SID for the group on SERV2 is 0x00002.
I'm guessing that you have registered the group as a login prior to restoring the database. You'll then have a row in sys.server_principals with SID 0x00002.
When you restore the database, you also restore the database users, and when you query sys.database_principals the user will still have SID 0x00001. There is now a mismatch between the SID of database user and the SID of the login.
What we need to do is update the SID of the database user to the SID of the login.
This is done by executing ALTER USER <user name> WITH LOGIN=<login name>. This will set the SID of the user to the SID of the login.
Hope this makes sense.
September 26, 2012 at 10:45 am
@Nils- yes, makes perfect sense, thanks for the detailed explanaiton
@SQLDBA360 - I agree 100%; we are trying to get away from local groups and move to AD authentication but that process moves slowly in my organization; this is (hopefully?) an interim solution.
thanks for the responses, everyone...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply