Roles in SQL Database

Question

Regarding the Roles in SQL Database. We want to grant a user a role that allows the following actions:

1.Access to all schemas within the database

2.Ability to create views on specific schemas

3.Ability to grant SELECT permissions on created views to specific users

4.Ability to delete the created views

Is it correct that the minimal roles to fulfill these conditions are "db_datawriter, db_datareader, db_ddladmin"? If there are more appropriate roles, please let us know. Additionally, if these three roles are granted, what operations are not permitted compared to the "db_owner" role? PS - Based on common issues that we have seen from customers and other sources, we are posting these questions to help the Azure community.

Answer 1

Greetings!

To grant a user the necessary permissions to:

1.      Access all schemas within the database.

2.      Create views on specific schemas.

3.      Grant SELECT permissions on created views to specific users.

4.      Delete the created views.

You need to assign specific roles and permissions. Here's the correct sequence of steps:

Step-by-Step Instructions:

1.      Create the User:

CREATE USER roletest WITH PASSWORD = 'xxx';

2.      Grant Required Roles and Permissions:

Assign db_ddladmin role:

ALTER ROLE db_ddladmin ADD MEMBER roletest;

Assign db_securityadmin role:

ALTER ROLE db_securityadmin ADD MEMBER roletest;

Grant SELECT permissions for all schemas:

GRANT SELECT ON SCHEMA::SalesLT TO roletest;

Grant CREATE VIEW and ALTER permissions on specific schemas:

GRANT CREATE VIEW TO roletest;

GRANT ALTER ON SCHEMA::SalesLT TO roletest;

Test Permissions:

Test SELECT on all schemas:

SELECT * FROM SalesLT.Address;

Test CREATE VIEW on specific schema:

CREATE VIEW SalesLT.address_view AS SELECT * FROM SalesLT.Address;

Grant SELECT permissions on created view to another role/user:

GRANT SELECT ON OBJECT::SalesLT.address_view TO roleview;

Drop the created view:

DROP VIEW SalesLT.address_view;

 

Explanation:

·        db_ddladmin: Allows creating and modifying database objects, including views.

·        db_securityadmin: Allows granting and revoking permissions on database objects.

·        GRANT SELECT ON SCHEMA::SalesLT: Ensures the user can read data from the SalesLT schema.

·        GRANT CREATE VIEW: Allows the user to create views in the database.

·        GRANT ALTER ON SCHEMA::SalesLT: Allows altering objects in the SalesLT schema, necessary for creating views in that schema.

This combination of roles and explicit grants should meet the requirements without granting excessive permissions like db_owner

Regards

Geetha