Skip to content

grant-permissions-on-a-stored-procedure.md

Latest commit

 

History

History
109 lines (71 loc) · 4.89 KB

grant-permissions-on-a-stored-procedure.md

File metadata and controls

109 lines (71 loc) · 4.89 KB
title Grant Permissions on a Stored Procedure
description Learn how to grant permissions on a stored procedure in SQL Server by using SQL Server Management Studio or Transact-SQL.
author WilliamDAssafMSFT
ms.author wiassaf
ms.reviewer vanto
ms.date 08/07/2025
ms.service sql
ms.subservice stored-procedures
ms.topic how-to
ms.custom
UpdateFrequency5
ignite-2025
helpviewer_keywords
stored procedures [SQL Server], permissions
monikerRange >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb

Grant Permissions on a Stored Procedure

[!INCLUDE SQL Server Azure SQL Database Synapse Analytics PDW FabricSQLDB]

This article describes how to grant permissions on a stored procedure by using [!INCLUDE ssManStudioFull] or [!INCLUDE tsql]. Permissions can be granted to an existing user, database role, or application role in the database.

Limitations

Permissions

The grantor (or the principal specified with the AS option) must have either the permission itself with GRANT OPTION, or a higher permission that implies the permission being granted. Requires ALTER permission on the schema to which the procedure belongs, or CONTROL permission on the procedure. For more information, see GRANT object permissions (Transact-SQL).

Use SQL Server Management Studio

Grant permissions on a stored procedure

  1. In Object Explorer, connect to an instance of [!INCLUDE ssDE] and then expand that instance.

  2. Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.

  3. Expand Stored Procedures, right-click the procedure to grant permissions on, and then select Properties.

  4. From Stored Procedure Properties, select the Permissions page.

  5. To grant permissions to a user, database role, or application role, select Search.

  6. In Select Users or Roles, select Object Types to add or clear the users and roles you want.

  7. Select Browse to display the list of users or roles. Select the users or roles to whom permissions should be granted.

  8. In the Explicit Permissions grid, select the permissions to grant to the specified user or role. For a description of the permissions, see Permissions (Database Engine).

Selecting Grant indicates the grantee will be given the specified permission. Selecting Grant With indicates that the grantee will also be able to grant the specified permission to other principals.

Use Transact-SQL

Grant permissions on a stored procedure

  1. Connect to the [!INCLUDE ssDE].

  2. From the tool bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute. This example grants EXECUTE permission on the stored procedure HumanResources.uspUpdateEmployeeHireInfo to an application role named Recruiting11.

USE AdventureWorks2022;
GRANT EXECUTE ON OBJECT::HumanResources.uspUpdateEmployeeHireInfo
    TO Recruiting11;
GO

Grant permissions on all stored procedures in a schema

  1. Connect to the [!INCLUDE ssDE].

  2. From the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute. This example grants EXECUTE permission to all stored procedures that exist, or will exist, in the HumanResources schema, to an application role named Recruiting11.

USE AdventureWorks2022;
GRANT EXECUTE ON SCHEMA::HumanResources
    TO Recruiting11;
GO

Related content