Grant a Permission to a Principal
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
This topic describes how to grant permission to a principal in SQL Server by using SQL Server Management Studio or Transact-SQL.
In This Topic
Before you begin:
To grant permission to a principal, using:
Before You Begin
Limitations and Restrictions
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.
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. Members of the sysadmin fixed server role can grant any permission.
Using SQL Server Management Studio
To grant permission to a principal
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)
Expand the Programmability folder.
Expand the Stored Procedures folder.
Right-click a stored procedure and select Properties.
In the Stored Procedure Properties -stored_procedure_name dialog box, under select a page, select Permissions. Use this page to add users or roles to the stored procedure and specify the permissions those users or roles have.
When finished, click OK.
Using Transact-SQL
To grant permission to a principal
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute.
-- 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).