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 –
--------------------------------------------------------------------------------------------------------------
Example #1 :
Providing “Select” permission on Data warehouse to specific user in Azure Synapse Analytics using “sp_addrolemember”
EXEC sp_addrolemember 'db_datareader' , 'UserName'
--------------------------------------------------------------------------------------------------------------
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'
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
Example #2:
Providing “Select” permission on Data warehouse to specific user in Azure Synapse Analytics using “GRANT DATABASE PRINCIPAL” permissions
GRANT SELECT ON DATABASE::[SQL pool Name] TO [UserName]
--------------------------------------------------------------------------------------------------------------
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]'
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
NOTE: To remove the permissions or user roles, below syntax can be used
“REVOKE ON DATABASE PERMISSION”
--------------------------------------------------------------------------------------------------------------
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 -
Providing “Select” permission to a SQL Schema on Data warehouse to specific user in Azure Synapse Analytics using “GRANT DATABASE PRINCIPAL” permissions
GRANT SELECT ON SCHEMA::[SCHEMA NAME] TO [UserName]
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
Example #4:
Note: There are multiple tables created with different schemas in the test environment -
Providing “Select” permission to a SQL Object on Data warehouse to specific user in Azure Synapse Analytics using “GRANT DATABASE PRINCIPAL” permissions
GRANT SELECT ON OBJECT::SCHEMANAME.TABLENAME TO [UserName]
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.