I am a bit confused on the permissions required to execute stored procedure. For example, if you have 2 tables and a stored procedure which reads data from Table1 and inserts data into Table2, will execute permission on the stored procedure and select permission on the tables be enough? In theory I am finding that this is given as a solution but practically I am finding that a user would need execute permission on the stored procedure, select permission on the tables and insert permission on Table2.
-
3If you are finding more than just permissions to execute on the proc are needed, it means the ownership chain is broken. Are the objects in the same database and schema?– Dan GuzmanApr 28, 2019 at 13:06
-
1for me, the error happened because in the stored procedure I was selecting from another DB.– Saeid OstadMay 27, 2021 at 1:27
2 Answers
No, you don't need to grant explicit permission on Table1 and Table2, that's one of the objective of embedding code in stored procedure and that's where encapsulation feature comes into effect.
Please check below link from Microsoft:
Managing Permissions with Stored Procedures in SQL Server
Stored Procedure Execution
Stored procedures take advantage of ownership chaining to provide access to data so that users do not need to have explicit permission to access database objects. An ownership chain exists when objects that access each other sequentially are owned by the same user. For example, a stored procedure can call other stored procedures, or a stored procedure can access multiple tables. If all objects in the chain of execution have the same owner, then SQL Server only checks the EXECUTE permission for the caller, not the caller's permissions on other objects. Therefore you need to grant only EXECUTE permissions on stored procedures; you can revoke or deny all permissions on the underlying tables.
Use the code below to grant execute permission:
USE database_name
GO
GRANT EXECUTE ON USP_NAME
TO User_name;
GO
Hope above helps.
There is at least one exception to Learning_DBAdmin's correct answer that I know of: dynamic SQL
Imagine usp_Proc1
that looks like this:
CREATE PROCEDURE dbo.usp_Proc1
AS
SELECT TOP (10) FROM dbo.Users;
If you have "EXECUTE" permissions to usp_Proc1
, then calling EXEC dbo.usp_Proc1
will work just fine.
However, if you change the implementation of the procedure to use dynamic SQL:
CREATE PROCEDURE dbo.usp_Proc1
AS
EXEC sp_executesql 'SELECT TOP (10) FROM dbo.TableA';
The call to EXEC dbo.usp_Proc1;
will fail with a permissions error unless you also grant the user SELECT
permissions on dbo.TableA
.
Running code with sp_executesql or EXECUTE like that creates a new "batch" outside of the context of the execution of the stored procedure.
Generally, this is because of the way ownership chaining works, which is described nicely here: Broken ownership chain – Dynamic SQL