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


SQL Orphaned users


SQL Orphaned users

Author
Message
pskoasha
pskoasha
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 268
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
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15123 Visits: 14396
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
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