0

Some background which might help; for each new system we are developing, we have three groups of databases: 

dbgDev  [Sys1_dev, Sys2_dev, Sys3_dev]
dbgTest [Sys1_test, Sys2_test, Sys3_test]
dbgProd [Sys1_prod, Sys2_prod, Sys3_prod]

and different user groups:

ugDeveloper [developer1, developer2, developer3]
ugTester    [tester1, tester2, tester3, ugDeveloper]
ugSupport   [support1, support2, ugDeveloper]
ugProd      [user1, user2, user3]

and each of these user groups require different permissions for each of the groups of databases:

ugDeveloper has pDevelop permissions on the dbgDev  group of databases
ugTester    has pTest    permissions on the dbgTest group of databases
ugSupport   has pSupport permissions on the dbgProd group of databases
ugProd      has pProd    permissions on the dbgProd group of databases

What we would like to be able to do is to add/remove users from a user group and they would then gain/lose the relevant permissions to the related databases. I have looked at using or creating Database and Server roles but it was not obvious to me how to organise the roles to create the structure we desire. I have contacted our DBA's some time ago but I'm still waiting for an answer from them. So I thought I would post it here and see if anyone could suggest how this could be done.

I did find a possible answer on Stack Overflow but I'm not sure it fully addresses our situation.

We are using Microsoft SQL Server Standard Edition (64-bit), version 10.50.2500 SP1.

Any advice on this would be appreciated. Many thanks, Garrie

MoonBrain
  • 11
  • 5
  • When you say you want to "add/remove users from a user group", are you talking about adding and removing users from an ActiveDirectory group, from a database role, or something else? – Mike Sherrill 'Cat Recall' May 28 '14 at 12:40
  • By "add/remove users from a user group" I'm referring to the different example user groups listed in the text (ugDeveloper, ugTester, ...). How this would be implemented in reality I'm not sure but believe they would be database roles. – MoonBrain May 28 '14 at 13:34

0 Answers0