Grant Permissions on a Stored Procedure

This topic describes how to grant permissions on a stored procedure in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. Permissions can be granted to an existing user, database role, or application role in the database.

In This Topic

• Before you begin:

  Limitations and Restrictions

  Security

• To grant permissions on a stored procedure, using:

  SQL Server Management Studio

  Transact-SQL

Before You Begin

Limitations and Restrictions

• You cannot use SQL Server Management Studio to grant permissions on system procedures or system functions. Use GRANT Object Permissions instead.

Security

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).

[Top]

Using SQL Server Management Studio

To grant permissions on a stored procedure

1. In Object Explorer, connect to an instance of Database Engine 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 click Properties.

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

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

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

7. Click 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.

[Top]

Using Transact-SQL

To grant permissions on a stored procedure

1. Connect to the Database Engine.

2. From the Standard bar, click New Query.

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

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

[Top]

See Also

Reference

sys.fn_builtin_permissions (Transact-SQL)

GRANT Object Permissions (Transact-SQL)

Concepts

Create a Stored Procedure

Modify a Stored Procedure

Delete a Stored Procedure

Rename a Stored Procedure