Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Local group permissions issues Expand / Collapse
Author
Message
Posted Monday, September 24, 2012 8:29 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 2:53 PM
Points: 316, Visits: 534
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
Post #1363524
Posted Monday, September 24, 2012 8:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
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
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

Post #1363535
Posted Monday, September 24, 2012 8:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:53 AM
Points: 6,742, Visits: 14,381
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"
Post #1363550
Posted Monday, September 24, 2012 8:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 2:53 PM
Points: 316, Visits: 534
The names are different, but the groups serve the same purpose.
Post #1363551
Posted Tuesday, September 25, 2012 1:24 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 1,890, Visits: 3,472
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>
Post #1364256
Posted Tuesday, September 25, 2012 1:28 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 2:53 PM
Points: 316, Visits: 534
Nils, are you saying I need to update the SID on the 'restored' server with the SID of the 'restoring' server?
Post #1364258
Posted Wednesday, September 26, 2012 1:16 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 1:23 AM
Points: 728, Visits: 790
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
Post #1364471
Posted Wednesday, September 26, 2012 2:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 1,890, Visits: 3,472
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.
Post #1364507
Posted Wednesday, September 26, 2012 10:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 2:53 PM
Points: 316, Visits: 534
@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...
Post #1364834
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse