May 10, 2013 at 1:11 am
Hi
firstly thanks for looking at this post.....
I am trying to implement a solution to fix our current security dilemma….i have created some security groups in AD where users are added/removed so they can gain access to SQL servers eg...(G.SqlReadOnly.Servername) this works fine until I either refresh a Database from a live server to its test counterpart or create a new one on said server. I have to then go in manually to map the group to the new/refreshed DB.
i have half a dozen other groups so this can get quite tiresome...Is there an easy way I can do this?
Thanks Pete
May 10, 2013 at 12:18 pm
pskoasha (5/10/2013)
Hifirstly thanks for looking at this post.....
I am trying to implement a solution to fix our current security dilemma….i have created some security groups in AD where users are added/removed so they can gain access to SQL servers eg...(G.SqlReadOnly.Servername) this works fine until I either refresh a Database from a live server to its test counterpart or create a new one on said server. I have to then go in manually to map the group to the new/refreshed DB.
i have half a dozen other groups so this can get quite tiresome...Is there an easy way I can do this?
Thanks Pete
A couple things:
1. Your group naming convention (e.g. G.SqlReadOnly.Servername) points to a problem with your approach. Tying AD Group Names to the server name means you might not be thinking of people in terms of what "role" they play within the environment and instead are thinking strictly about what they themselves should be able to access. When considering Groups and Group Membership try thinking about what logical roles exist in your environment, e.g. Accounting.Auditing. With a group name like this an accountant might need read-only access to data on the specific server name you showed, so that group would be granted read-only access. This way when a new accountant joins they are added to the groups that logically describe their role-responsibilities and they magically get access to the resources they need to do their jobs but nowhere in that approach does a person get tied into referencing a specific server name. Server names change far more often than the logical roles within a company.
2. More towards your original question: an Active Directory Group is issued an SID when the Group is created. When we create a SQL Server Login based on an Active Directory Group the SID from the Group is used as the SID in SQL Server. You can see these using this query:
SELECT name,
sid
FROM sys.server_principals
WHERE name = 'Domain\GroupName';
When you restore a database from one instance to another if both instances have a Login based on the same Active Directory Group then orphans should not occur because the SIDs should be aligned. Are you restoring the database to a different domain where the names may be the same but the SIDs may be misaligned?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply