SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Local group permissions issues


Local group permissions issues

Author
Message
Jason Marshall
Jason Marshall
Old Hand
Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)

Group: General Forum Members
Points: 378 Visits: 569
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
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10052 Visits: 6324
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



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19780 Visits: 17242
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" ;-)
Jason Marshall
Jason Marshall
Old Hand
Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)

Group: General Forum Members
Points: 378 Visits: 569
The names are different, but the groups serve the same purpose.
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2433 Visits: 3575
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>
Jason Marshall
Jason Marshall
Old Hand
Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)

Group: General Forum Members
Points: 378 Visits: 569
Nils, are you saying I need to update the SID on the 'restored' server with the SID of the 'restoring' server?
heymiky
heymiky
SSC Eights!
SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)

Group: General Forum Members
Points: 921 Visits: 845
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
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2433 Visits: 3575
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.
Jason Marshall
Jason Marshall
Old Hand
Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)

Group: General Forum Members
Points: 378 Visits: 569
@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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search