SQL permissions based on multiple AD Groups

  • 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...

    • PublicSchema - public (anyone)
    • DevSchema (DEVS role containing SQLDevs AD Group)
    • SecretSchema (CLEARED role containing SecurityCleared AD Group)
    • AuditSchema (DEVS_CLEARED role containing ???) - has to for members of both SQLDevs and SecurityCleared AD Groups

    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

  • 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]

  • Ant-Green wrote:

    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

  • 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

  • Ant-Green wrote:

    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

  • 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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply