Permissions for Stored Procedures
Understanding the permission model for stored procedures is essential for securing database operations while allowing necessary access to users and roles.
Required Permissions
To create, alter, or drop a stored procedure, a principal must have one of the following permissions on the schema that contains the procedure:
| Action | Required Permission |
|---|---|
| Create | CREATE PROCEDURE or ALTER on the schema |
| Alter | ALTER on the schema |
| Drop | DROP on the schema |
| Execute | EXECUTE on the procedure or on the containing schema |
Granting EXECUTE Permission
The most common scenario is granting users the ability to execute a stored procedure without exposing underlying tables.
Show T‑SQL Example
GRANT EXECUTE ON OBJECT::dbo.uspGetEmployeeDetails TO [HR_Readers];
GO
-- To revoke:
REVOKE EXECUTE ON OBJECT::dbo.uspGetEmployeeDetails FROM [HR_Readers];
GOOwnership Chaining
When a stored procedure accesses objects (tables, views) within the same schema, SQL Server uses an ownership chain. If the owner of the procedure and the accessed objects is the same, users only need EXECUTE permission on the procedure.
Best Practices
- Grant EXECUTE at the schema level when many procedures share the same security requirements.
- Use roles to group users with similar permission needs.
- Limit the use of
WITH EXECUTE ASto specific scenarios.
Example: Granting Schema‑Level EXECUTE
Show T‑SQL Example
GRANT EXECUTE ON SCHEMA::dbo TO [ReportingRole];
GO
-- Verify:
SELECT permission_name, state_desc
FROM sys.database_permissions
WHERE major_id = SCHEMA_ID('dbo')
AND grantee_principal_id = USER_ID('ReportingRole');