3

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. enter image description here What should I use to achieve this? How do you combine logins? (AD groups)?

THank you very much in advance

1
  • Thank you for your answers so far, so there is no way to actually just manage one group/role and add everyone that role in order to minimize management of the permissions? Nov 7 '16 at 15:21
1

You need to do the user/role mapping per database.

So you create an AD group and add that as a login on the server

Within the database you define a role and set permissions for the role. Then you need to add the AD Group login as a user to the database and set them as a member of the database role. Each role can have multiple members so you can add many groups as members of the role.

You can also map the users to a server role and then add the server role as a user in the database and make that member of a database role.

Robin Page has a simple explanation on simple talk and it's all explained in details in multiple details in many StackExchange answers

3
  • Thank you for your answer, so there is no way to actually just manage one group/role and add everyone that role in order to minimize management of the permissions? Nov 7 '16 at 15:20
  • You can add an ad group to a custom server role and then add that role to custom database role. Or if everyone that connects to the server should have the same permission just grant the permissions to the public server role.
    – Spörri
    Nov 7 '16 at 15:38
  • Giving permission to public is still not considered best practices. It's there for completeness, not necessarily as recommendations
    – Spörri
    Nov 7 '16 at 15:44
0

You'll need to create the Role within the Database, give that Role permissions, then Assign your AD Groups to that Role.

For example, you could have an Admin role with Read/Write/Execute and a Support role with Read Only.

USE [DB]

GO
--Create your roles in the database

CREATE ROLE MyAdminRole AUTHORIZATION dbo;
GRANT EXECUTE, ALTER, CONTROL ON SCHEMA :: dbo TO [MyAdminRole];

CREATE ROLE MySupportRole AUTHORIZATION dbo;
GRANT SELECT ON SCHEMA :: dbo TO [MySupportRole];

--Now Create our Users in the DB
CREATE USER [MyDomain\MyAdminGroup] FOR LOGIN [MyDomain\MyAdminGroup];
CREATE USER [MyDomain\MySupportGroup] FOR LOGIN [MyDomain\MySupportGroup];

--Now Map our AD Group to our Role
EXEC sp_addrolemember N'MyAdminRole', 'MyDomain\MyAdminGroup';
EXEC sp_addrolemember N'MySupportRole', 'MyDomain\MySupportGroup';

Though I didn't in this example, you could have have multiple AD groups as members of the Role. 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.