Disclaimer: I inherited this setup, I didn't design it
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.
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 months, 3 weeks ago by elliotmajor.