Local group permissions issues

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » Security (SS2K8) » Local group permissions issues

Local group permissions issues

Rate Topic

Display Mode

Topic Options

Author

Message

Jason Marshall

Posted Monday, September 24, 2012 8:29 AM

Old Hand

Group: General Forum Members
Last Login: Monday, June 10, 2013 9:42 AM
Points: 300, Visits: 430

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

anthony.green

Posted Monday, September 24, 2012 8:38 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:40 AM
Points: 5,075, Visits: 4,833

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

Perry Whittle

Posted Monday, September 24, 2012 8:52 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:39 PM
Points: 5,242, Visits: 11,255

Jason Marshall (9/24/2012)

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" Wink

Post #1363550

Jason Marshall

Posted Monday, September 24, 2012 8:54 AM

Old Hand

Group: General Forum Members
Last Login: Monday, June 10, 2013 9:42 AM
Points: 300, Visits: 430

The names are different, but the groups serve the same purpose.

Post #1363551

Nils Gustav Stråbø

Posted Tuesday, September 25, 2012 1:24 PM

SSCommitted

Group: General Forum Members
Last Login: Today @ 12:30 AM
Points: 1,788, Visits: 3,330

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

Jason Marshall

Posted Tuesday, September 25, 2012 1:28 PM

Old Hand

Group: General Forum Members
Last Login: Monday, June 10, 2013 9:42 AM
Points: 300, Visits: 430

Nils, are you saying I need to update the SID on the 'restored' server with the SID of the 'restoring' server?

Post #1364258

SQLDBA360

Posted Wednesday, September 26, 2012 1:16 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 9:31 AM
Points: 643, Visits: 693

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

Nils Gustav Stråbø

Posted Wednesday, September 26, 2012 2:59 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 12:30 AM
Points: 1,788, Visits: 3,330

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

Jason Marshall

Posted Wednesday, September 26, 2012 10:45 AM

Old Hand

Group: General Forum Members
Last Login: Monday, June 10, 2013 9:42 AM
Points: 300, Visits: 430

@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 »

Permissions