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

LearnByReading
  • 711
  • 10
  • 26

2 Answers2

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

Community
  • 1
Spörri
  • 4,734
  • 15
  • 28
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.
Ryan Cooper
  • 416
  • 2
  • 4