April 20, 2018 at 7:13 am
Hi Guys
My manager has asked me to tighten up on security in our SQL environment.....hairy muff...but what he want's i'm not sure i can give....i have tried
he wants security groups in AD to be used in SQL to authenticate.... this is the easy part and i have tested and it is working.
However, when it comes to DB mapping, how do i map DB's to certain individuals and not the entire group???
I.E.....User A (among others) is in the "Contractor Login" AD group that i have created to authenticate. I want user A to be mapped to DB X and Y with data reader/data writer permissions......
The only way i can achieve this, is to add that AD user as a SQL Server login, map that user to whatever DB i like with whatever permissions....
Hope this makes sense
Please help either tell my manager to get a clue if there is no way to achieve this other than my way
April 20, 2018 at 8:16 am
Well if he wants permissions more granular than AD groups then he has to use logins more granular than AD groups.
April 20, 2018 at 8:41 am
OR more granular AD groups.
April 20, 2018 at 10:06 am
I believe you can grant permissions to a specific member of an AD group in a database without granting that permission to the entire AD group in the database. You don't even need to add that member as a login on the instance as long as the AD group has a login. It will be difficult to manage though as individual members enter or leave the group, and the number of user specific permissions grows.
As a partial workaround if you can't get them to do more specific AD groups, is create database roles and assign individual AD group members to the database role with all permissions belonging to the role. Does this help?
April 23, 2018 at 1:28 am
Hey guys thanks for the suggestions so far.
After the weekend, I think i might have been a bit harsh about my manager.....
I agree with the Granular AD suggestion as this will work, could become a nightmare in the long run though.
@chris-2 Harshman, could you point me in a direction where you think you may have come across an article etc.
i don't mind doing my own reading and research.....
April 23, 2018 at 2:31 am
Why not just have one AD group for each required permission set? You might find that you have some groups that contain only one member, but that doesn't matter - requirements will change and you may find that you end up adding more users to those groups. Furthermore, if you insist on AD groups (and no individual access) from the start, it gets people used to the process.
John
April 23, 2018 at 4:16 am
The current place I am contracting is going through a similar process. The trick as others have said is to have more granular groups and just add/remove members as needed.
April 23, 2018 at 7:32 am
Thanks Guys.
So let me add a bit more info as to what i already have in place.
As an example...
I have these AD security groups:
BizTalk - contractor (populated with 3 contractors)
BizTalk - Sysadmin
Biztalk - Public / ReadOnly
Any users added to these groups will be able to log in to SQL server based on the permission assigned to that AD GROUP
BizTalk - Sysadmin - not a problem as that is only me
BizTalk - contractor - this is where i hit the wall, PUBLIC Server security permissions have been given to the group to allow users to log in.
- how do i now map or assign only contractor doofus, who is a member of the BizTalk - contractor AD Group, EXECUTE and CREATE PROCEDURE access to Database X , and contractor chop who is also a member of this group whatever permissions on Database Y
So...users in the same group.....needing different DB Security levels
please don't take the permissions literally, only an example of what i am trying to achieve...if possible
April 23, 2018 at 7:39 am
You just split BizTalk - Contractor into BizTalk - Contractor DBx, BizTalk - Contractor DBy, then give the correct permissions for each and add the user to which one(s) they require.
April 23, 2018 at 7:46 am
An interesting problem. I've seen this before, where the same user is a member of more than one group. I can't remember what the answer is, though! Does it:
(1) Aggregate the permissions
(2) Choose the most restrictive set of permissions
(3) Use some other algorithm to choose which group's permissions to use
Rick's solution above may work for you - but what if these groups are used outside SQL Server as well? It wouldn't be too easy then just to split them up. You probably need to do a bit of investigation here. I typed "sql multiple AD group membership" into a search engine and got some results that looked as if they may help you.
John
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply