Edit

Share via

Grant a Permission to a Principal

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL database in Microsoft Fabric Preview

This article describes how to grant permission to a principal in SQL Server by using SQL Server Management Studio or Transact-SQL.

Security

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.

Use SQL Server Management Studio

Grant permission to a principal

  1. In Object Explorer, expand the database that contains the object to which you want to grant permissions.

    Note

    These steps deal specifically with granting permissions to a stored procedure, but you can use similar steps to add permissions to tables, views, functions, and assemblies, as well as other securables. For more information, see GRANT (Transact-SQL)

  2. Expand the Programmability folder.

  3. Expand the Stored Procedures folder.

  4. Right-click a stored procedure and select Properties.

  5. In the Stored Procedure Properties dialog box, select the Permissions page. Use this page to add users or roles to the stored procedure and specify the permissions those users or roles have.

  6. When finished, select OK.

Use Transact-SQL

Grant permission to a principal

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, select New Query.

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

    -- Grants EXECUTE permission on stored procedure HumanResources.uspUpdateEmployeeHireInfo to an application role called Recruiting11.   
USE AdventureWorks2022;  
GO  
GRANT EXECUTE ON OBJECT::HumanResources.uspUpdateEmployeeHireInfo  
    TO Recruiting11;  
GO

For more information, see GRANT (Transact-SQL) and GRANT object permissions (Transact-SQL).

Limitations

Consider the following best practices that can make managing permissions easier.

  • Grant permission to roles, instead of individual logins or users. When one individual is replaced by another, remove the departing individual from the role and add the new individual to the role. The many permissions that might be associated with the role will automatically be available to the new individual. If several people in an organization require the same permissions, adding each of them to the role will grant them the same permissions.

  • Configure similar securables (tables, views, and procedures) to be owned by a schema, then grant permissions to the schema. For example, the payroll schema might own several tables, views, and stored procedures. By granting access to the schema, all the necessary permissions to perform the payroll function can be granted at the same time. For more information about what securables can be granted permissions, see Securables.

  • In SQL database in Microsoft Fabric Preview, Microsoft Entra ID for database users is the only supported authentication method. Server-level roles and permissions are not available, only database-level. For more information, see Authorization in SQL database in Microsoft Fabric.

Related content