Disclaimer: I inherited this setup, I didn't design it
Background
So I have 4 SQL Server 2017 instances/environments;
Access/Permissions to these environments is controlled by 4 active directory groups;
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
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?
January 14, 2020 at 7:33 pm
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:
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.
January 15, 2020 at 9:13 am
I should maybe have been a little clearer with my problem, the problem being that one user is in two AD groups, say
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
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply