Windows authentication group login changes

  • elliotmajor

    SSC-Addicted

    Points: 463

    Disclaimer: I inherited this setup, I didn't design it

    Background

    So I have 4 SQL Server 2017 instances/environments;

    • LIVE
    • SIM
    • TEST
    • DEV

    Access/Permissions to these environments is controlled by 4 active directory groups;

    • PROJ_OPERATIONS
    • PROJ_DEVOPS
    • PROJ_DEVELOPMENT
    • PROJ_TESTERS

    It's come up that users need different access levels depending on environment (what a surprise!)

    My idea for a solution is to instead of having, 4 AD groups for the entire project, we have 4 groups for each environment (for a total of 12) e.g

    • [LIVE/SIM/TEST/DEV]_PROJ_OPERATIONS
    • [LIVE/SIM/TEST/DEV]_PROJ_DEVOPS
    • [LIVE/SIM/TEST/DEV]_PROJ_DEVELOPMENT
    • [LIVE/SIM/TEST/DEV]_PROJ_TESTERS

    This would satisfy the requirement to have different permissions for different users based on environment, and personally the way I would have set it up given the chance.

    Question

    My issue is that the permissions structure for these environment-group pairs is pretty involved and not well documented.

    Would it be possible to create the new groups in AD, add the same members then simply run something like the below?

    ALTER LOGIN [DOMAIN\PROJ_OPERATIONS] WITH NAME = [DOMAIN\LIVE_PROJ_OPERATIONS]

    Or would the change in SID cause issues (assuming SQL doesn't re-validate on login name change?)

    Would I just be better off trying to script out the permissions using something like dbatools instead?

    • This topic was modified 5 days, 18 hours ago by  elliotmajor.
  • Chris Harshman

    SSC-Forever

    Points: 41904

    What may be a better approach, is just use the AD groups to identify people, then setup database roles for the levels of permissions you need to give people.  For example, you could have the following database roles:

    • DDL_Admin = ability to create items, alter items, grant permissions, etc
    • Power_User = write ability on most tables, read ability on most tables
    • Basic_User = write ability on some tables, read ability on most tables
    • Read_Only = read ability on most tables

    So the then in DEV environmnet, PROJ_DEVELOPMENT would have DDL_Admin role assigned to it, in TEST environment, PROJ_DEVELOPMENT may only have Basic_User assigned to it, in SIM environment, PROJ_DEVELOPMENT would have Read_Only assigned to it, etc.  I believe this will simplify the initial setup and maintenance going forward as people leave or join the project groups you only have to maintain one set of AD groups instead of 4.

    https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-2017

     

  • elliotmajor

    SSC-Addicted

    Points: 463

    I should maybe have been a little clearer with my problem, the problem being that one user is in two AD groups, say

    • PROJ_DEVOPS
    • PROJ_DEVELOPMENT

    The idea was that he need to be in DEVOPS on the live environment and DEVELOPMENT on the development environment, his membership to both causes issues due to explicit DENY permissions on the DEVELOPMENT account.

    However he's the special case being in both groups, I guess I could make a group for just him to get around it

  • Chris Harshman

    SSC-Forever

    Points: 41904

    Yes, that makes sense, and that is exactly the situation what I was proposing is designed to avoid.  If your DEVOPS people need different permissions in different environments, just have the people in the DEVOPS group and assign the DEVOPS group to different database roles in each environment.

Viewing 4 posts - 1 through 4 (of 4 total)

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