Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

INTRODUCTION:

In this session, we will learn how to create custom roles as a replacement solution for db_reader,db_writer and DDL admin fixed database level roles.

Please refer this to get an idea on how to create login and users first.

Custom roles are created when group of users are required to be restricted from particular database objects and it is used to implement\harden access security policy.

As we know, in Azure Synapse analytics SQL pool, we have SQL pool name as an equivalent name for on-premises SQL sever database name and that is visible on SSMS object explorer.

Inside that SQL pool, we have schemas, tables etc…

let us say, we want some users not to see some of the schema or Only have limited access on the schema inside the SQL pool, that is possible and doable.

Here, we will see step by step approach to get the same.

Before jump into this, let us query below code to see what all users and their permissions exist on the server.

----- Find out Db users and its role permission



SELECT DP1.name AS DatabaseRoleName,   
   isnull (DP2.name, 'No members') AS DatabaseUserName   
 FROM sys.database_role_members AS DRM  
 RIGHT OUTER JOIN sys.database_principals AS DP1  
   ON DRM.role_principal_id = DP1.principal_id  
 LEFT OUTER JOIN sys.database_principals AS DP2  
   ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = 'R'
ORDER BY DP1.name;  

For read-only schema access

Steps:

--Create custom role on schema level inside Azure Synapse DB pool

create role role_name;

--Add users to the specific custom role
EXEC sp_addrolemember '<Role_Name>', 'user’
;

--Grant below schema level read permissions to all schemas

select 'GRANT SELECT,view definition,execute ON SCHEMA::'+name +  ' TO Role_Name'+';' from sys.schemas

-- You can specify the schemas here which you want to hide it from user to see them in SSMS or to restrict them performing any operation.

select 'GRANT SELECT,view definition,execute ON SCHEMA::'+name +  ' TO Role_Name'+';' from sys.schemas where name not in ('NameOfSchemaToBeRestircted')

--It will prepare the statement for you and you can then execute the same.

For Db writer schema access

Steps:

--Create custom role on schema level inside Azure Synapse DB pool

create role role_name;

--Add users to the specific custom role
EXEC sp_addrolemember '<Role_Name>', 'user’
;


Grant below schema level permissions to required schemas


select 'GRANT delete,insert,update ON SCHEMA::'+name +  ' TO role_name'+';' from sys.schemas 
----It will prepare the statement for you and you can then execute the same.

For DDL admin schema access

Steps:

--Create custom role on schema level inside Azure Synapse DB pool

create role role_name;

--Add users to the specific custom role
EXEC sp_addrolemember '<Role_Name>', 'user’
;


GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO role_name;

Step 2:

Grant below schema level permissions to required schemas

select 'Grant ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, UPDATE, VIEW DEFINITION ON SCHEMA::'+name +  ' TO role_name'+';' from sys.schemas 

------It will prepare the statement for you and you can then execute the same.

Drop custom roles

To drop the custom roles we need to first Remove all users under the role
Example:-

exec sp_droprolemember ‘role name’,’user name’

Drop role ‘role name’