Granting the view permission for a stored procedure in a Synapse SQL pool

Question

Hi,

in a dedicated Synapse SQL pool is it to possible granting the view (and not the execution) permission for a stored procedure? Thanks

Answer 1

@pmscorca

Welcome to Microsoft Q&A platform and thanks for posting your question.

In Azure Synapse Analytics, you can grant or deny permissions on a securable (such as a database, table, view, etc.) to a security principal (a login, a database user, or a database role) using

the GRANT, DENY, and REVOKE statements. However, for stored procedures specifically, there isn’t a direct way to grant view-only permissions.

Typically, permissions for stored procedures are handled through the EXECUTE permission. This allows a user to execute the stored procedure, but it doesn’t provide the ability to view the procedure’s code

If you want to allow a user to see the definition of a stored procedure (which is similar to view permission), you can use the VIEW DEFINITION permission. This permission lets a user see the metadata of the securable on which the permission is granted. However, please note that this permission does not allow the user to execute the stored procedure.

Here is an example of how you can grant the VIEW DEFINITION permission to a role:

-- Step 1: Create a new role
CREATE ROLE view_definition_role;
-- Step 2: Grant the VIEW DEFINITION permission to the new role
GRANT VIEW DEFINITION TO view_definition_role;

After creating the role, you can add users to this role as needed.

Please note that these permissions should be granted carefully, considering the security implications. Always follow the principle of least privilege, granting only the permissions that are necessary for each user.

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.