I know there are two types of roles, one predefined (fixed), another user-defined (flexible). Here is my use case: I have a large number of AD Groups, and I am trying to map them up to a role and define permissions on that role. For example, ADGroup1, 2, 3 and 4 only should have access to database 1 tables 1, 2 and 3. To avoid re-defining the permission every single time for every single AD Group, I am trying to assign permissions to a ROLE and define the proper permissions to that single role.
Here is the issue I get: For Logins, when I get properties, I get this nice "User Mappings" page that lets me define which DBs a user can have access to. But I cannot do that with a role, I cannot pick and choose databases a role can have access to. What should I use to achieve this? How do you combine logins? (AD groups)?
THank you very much in advance