November 30, 2020 at 7:54 am
Hi
I'd like some input on the best way to handle setting permissions in SQL Server 2016 using 2 Active Directory Groups.
I have 2 AD Groups - SQLDevs and SecurityCleared. I have created database roles for DEVS, CLEARED and DEV_CLEARED.
I would like to apply permissions based on these Groups as follows...
I can't find a way to set the permissions to SchemaAudit. I've tried many scripted solutions none of which work well but would like to find a solution with no manual intervention.
Thanks in advance.
PaulF
November 30, 2020 at 8:21 am
Please explain clearly what is not working
ALTER ROLE [DEVS] ADD MEMBER [DOMAIN\SQLDevs]
ALTER ROLE [CLEARED] ADD MEMBER [DOMAIN\SecurityCleared]
ALTER ROLE [DEVS_CLEARED] ADD MEMBER [DOMAIN\SQLDevs]
ALTER ROLE [DEVS_CLEARED] ADD MEMBER [DOMAIN\SecurityCleared]
GRANT [permission (SELECT/INSERT/UPDATE/DELETE/VIEW/ALTER etc)] ON Schema::DevSchema TO [DEVS]
GRANT [permission (SELECT/INSERT/UPDATE/DELETE/VIEW/ALTER etc)] ON Schema::SecretSchema TO [CLEARED]
GRANT [permission (SELECT/INSERT/UPDATE/DELETE/VIEW/ALTER etc)] ON Schema::AuditSchema TO [DEVS_CLEARED]
November 30, 2020 at 9:07 am
Please explain clearly what is not working
ALTER ROLE [DEVS_CLEARED] ADD MEMBER [DOMAIN\SQLDevs]
ALTER ROLE [DEVS_CLEARED] ADD MEMBER [DOMAIN\SecurityCleared]
Doesn't this give permission to members of either Group? I need to have permissions assigned to members who are in BOTH groups (devs who are cleared).
Regards PF
November 30, 2020 at 9:14 am
Well then you need a 3rd security group
Devs - Cleared or not
Cleared - Users who are cleared
DevsCleared - Devs who are cleared
You will then need something in your JML(joiners movers leavers) process that people can only be added to these groups when the right clearance comes through
November 30, 2020 at 9:27 am
Well then you need a 3rd security group
Devs - Cleared or not
Cleared - Users who are cleared
DevsCleared - Devs who are cleared
You will then need something in your JML(joiners movers leavers) process that people can only be added to these groups when the right clearance comes through
That's what I was worried about. Unfortunately the two Groups are maintained by separate parts of the business so the maintenance of the third Group would be a manual process anyway.
Thanks though. PF
November 30, 2020 at 9:58 am
Then you would want to do some PowerShell magic and automate it.
Use Get-AdGroupMember and pull the information down from the 3 groups.
Use a filter to get who is in Devs, who is in Cleared (The list of people who should be in DevsCleared) (like an Inner-Join)
Use that combined list to filter out who is not in DevsCleared and then use Add-AdGroupMember to push in the missing people (like a Left-Join where DevsCleared IS NULL)
Something like the below would do it, remove 'PF','AG' etc and replace it with the code to the right of the #
[string[]]$Devs = 'PF','AG','RandomDev' #Get-AdGroupMember -Identity Devs
[string[]]$Cleared = 'PF','AG','RandomUser' #Get-AdGroupMember -Identity Cleared
[string[]]$DevsCleared = 'AG' #Get-AdGroupMember -Identity DevsCleared
#Do an inner join between Dev group and Cleared group to get matches
[string[]]$DevsWhoAreClearedInnerJoin = $Devs | Where {$Cleared -Contains $_}
#Do a left join between the inner join result set and the AD Group DevCleared to get the missing cleared devs
[string[]]$DevsToAddToDevsCleared = $DevsWhoAreClearedInnerJoin | Where {$DevsCleared -NotContains $_}
#Add the members to the group
$DevsToAddToDevsCleared #Add-AdGroupMember -Identity DevsCleared -Members $DevsToAddToDevsCleared
RESULT SET = PF as they are in Devs and in Cleared, but not in DevsCleared
[string[]]$Devs = Get-AdGroupMember -Identity Devs
[string[]]$Cleared = Get-AdGroupMember -Identity Cleared
[string[]]$DevsCleared = Get-AdGroupMember -Identity DevsCleared
#Do an inner join between Dev group and Cleared group to get matches
[string[]]$DevsWhoAreClearedInnerJoin = $Devs | Where {$Cleared -Contains $_}
#Do a left join between the inner join result set and the AD Group DevCleared to get the missing cleared devs
[string[]]$DevsToAddToDevsCleared = $DevsWhoAreClearedInnerJoin | Where {$DevsCleared -NotContains $_}
#Add the members to the group
Add-AdGroupMember -Identity DevsCleared -Members $DevsToAddToDevsCleared
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply