Read Only Permissions in Synapse SQL

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

The Object level / Schema level permissions can be provided in Azure Synapse Analytics using SQL statements. There are be several ways that this can be achieved with different SQL syntax.

 

EXEC sp_addrolemember 'db_datareader' , 'UserName'

 

The Syntax “sp_addrolemember” Adds a database user, database role, Windows login, or Windows group to a database role in the current database.

 

The Syntax “db_datareader” Members of the db_datareader fixed database role can read all data from all user tables.

 

GRANT Database Principal Permissions also another way that can be achieved this task. This can be extended to table / schema level permissions.

 

GRANT SELECT ON DATABASE::[SQL pool Name] TO [UserName] 

--------------------------------------------------------------------------------------------------------------

For Testing purposes, we have created 10 tables in the Data warehouse as follows –

 

Image 1.png

 

--------------------------------------------------------------------------------------------------------------

Example #1 :

Providing “Select” permission on Data warehouse to specific user in Azure Synapse Analytics using “sp_addrolemember”

  • Connect to Data warehouse using a user with ALTER USER permissions & use below syntax to provide select permissions to the SQL pool –

          EXEC sp_addrolemember 'db_datareader' , 'UserName'

 

Image 2.png

 

--------------------------------------------------------------------------------------------------------------

  • The role permission level can be check after providing the access to the specific user using “sp_addrolemember” as follows -

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.name ='db_datareader'

 

Image 3.png

 

--------------------------------------------------------------------------------------------------------------

  • The role permission level verifications as follows  –

 

Image 4.png

 

--------------------------------------------------------------------------------------------------------------

 

Image 5.png

 

--------------------------------------------------------------------------------------------------------------

 

Image 6.png

--------------------------------------------------------------------------------------------------------------

 

Image 7.png

 

--------------------------------------------------------------------------------------------------------------

 

Example #2:

Providing “Select” permission on Data warehouse to specific user in Azure Synapse Analytics using “GRANT DATABASE PRINCIPAL” permissions

 

  • Connect to SQL pool using a user with ALTER USER permissions & use below syntax to provide select permissions to the SQL pool –

         GRANT SELECT ON DATABASE::[SQL pool Name] TO [UserName] 

 

Image 8.png

--------------------------------------------------------------------------------------------------------------

 

  • The permission level can be check after providing the access to the specific user using “GRANT SELECT ON DATABASE” as follows -

select  princ.name,

princ.type_desc,

perm.permission_name,

perm.state_desc,

perm.class_desc,

object_name(perm.major_id)

from

sys.database_principals princ

left join sys.database_permissions perm

on perm.grantee_principal_id = princ.principal_id

where princ.name = '[USERNAME IN DW]'

 

Image 9.png

 

--------------------------------------------------------------------------------------------------------------

  • The GRANT DATABASE PRINCIPAL permission level verifications as follows –

Image 10.png

 

--------------------------------------------------------------------------------------------------------------

 

Image 11.png

 

--------------------------------------------------------------------------------------------------------------

 

Image 12.png

--------------------------------------------------------------------------------------------------------------

 

Image 13.png

 

--------------------------------------------------------------------------------------------------------------

 

NOTE: To remove the permissions or user roles, below syntax can be used

 

  • To drop a user from user role –

          “sp_droprolemember

 

Image 14.png

 

  • To drop a database principal permission –

         “REVOKE ON DATABASE PERMISSION

 

Image 15.png

 

--------------------------------------------------------------------------------------------------------------

NOTE : When “Select” permissions are provided, the database objects will be visible to the user who has permissions and to hide the objects and provide access to specific objects, this needs to be extended to object level / schema level permissions

 

Example #3:

Note: There are multiple tables created with different schemas in the test environment -

 

Image 16.png

 

Providing “Select” permission to a SQL Schema on Data warehouse to specific user in Azure Synapse Analytics using “GRANT DATABASE PRINCIPAL” permissions

 

  • Connect to Data warehouse using a user with ALTER USER permissions & use below syntax to provide select permissions to the SQL Schema & verify –

          GRANT SELECT ON SCHEMA::[SCHEMA NAME] TO [UserName] 

 

Image 17.png

 

--------------------------------------------------------------------------------------------------------------

 

Image 18.png

 

--------------------------------------------------------------------------------------------------------------

  • Verifications using SQL server Object Explorer once the user is connected. The user will be able to see the objects under the schema (that permission provided)

 

Image 19.png

 

--------------------------------------------------------------------------------------------------------------

Example #4:

Note: There are multiple tables created with different schemas in the test environment -

 

Image 21.png

 

Providing “Select” permission to a SQL Object on Data warehouse to specific user in Azure Synapse Analytics using “GRANT DATABASE PRINCIPAL” permissions

 

  • Connect to Data warehouse using a user with ALTER USER permissions & use below syntax to provide select permissions to the SQL Object & verify –

         GRANT SELECT ON OBJECT::SCHEMANAME.TABLENAME TO [UserName] 

 

Image 22.png

 

--------------------------------------------------------------------------------------------------------------

 

Image 23.png

 

--------------------------------------------------------------------------------------------------------------

  • Verifications using SQL server Object Explorer once the user is connected. The user will be able to see the objects under the schema (that permission provided)

 

Image 24.png

 

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.