Azure Synapse Analytics content

Azure_SA.md

Azure Synapse Analytics

Table of contents

1. Grants

Grants

1. Documentation: https://learn.microsoft.com/en-us/sql/t-sql/statements/permissions-grant-deny-revoke-azure-sql-data-warehouse-parallel-data-warehouse
2. Check current permissions using T-SQL

SELECT DISTINCT
    pr.principal_id, pr.name AS [UserName], pr.type_desc AS [User_or_Role],
    pr.authentication_type_desc AS [Auth_Type], pe.state_desc,
    pe.permission_name, pe.class_desc, COALESCE(o.[name], s.[name]) AS 'Object'
FROM sys.database_principals AS pr
         JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id
         LEFT JOIN sys.objects AS o on (o.object_id = pe.major_id)
         LEFT JOIN sys.schemas AS s on (s.schema_id = pe.major_id);

3. Example of GRANT SELECT for a SCHEMA:

GRANT SELECT ON SCHEMA::[<SCHEMA NAME>] to [<USER NAME>];

4. Example of REVOKE SELECT for a SCHEMA:

REVOKE SELECT ON SCHEMA::[<SCHEMA NAME>] to [<USER NAME>];