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

SQL Orphaned users Expand / Collapse
Author
Message
Posted Friday, May 10, 2013 1:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 2:49 AM
Points: 7, Visits: 222
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
Post #1451433
Posted Friday, May 10, 2013 12:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:40 PM
Points: 7,082, Visits: 12,576
pskoasha (5/10/2013)
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

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
Post #1451709
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse