Stored Procedure and Permissions - Is EXECUTE enough?

Question

I have a SQL Server 2008 database where all access to the underlying tables is done through stored procedures. Some stored procedures simply SELECT records from the tables while others UPDATE, INSERT, and DELETE.

If a stored procedure UPDATES a table does the user executing the stored procedure also need UPDATE permissions to the affected tables or is the fact that they have EXECUTE permissions to the stored procedure enough?

Basically I am wondering if giving the user EXECUTE permissions to the stored procedures is enough or do I need to give them SELECT, UPDATE, DELETE, and INSERT permissions to the tables in order for the stored procedures to work. Thank you.

[EDIT] In most of my stored procedures it does indeed appear that EXECUTE is enough. However, I did find that in stored procedures where "Execute sp_Executesql" was used that EXECUTE was not enough. The tables involved needed to have permissions for the actions being performed within "sp_Executesql".

Osmium

I'm afraid that when using dynamic SQL queries you will need to grant permissions on the underlying object. I've updated the answer to reflect that. – Chickenlivered

@Noel - You are correct. I found that I needed to allow datareader access to the tables for the users. Thankfully that was not an issue for my situation since all users were allowed to view all data. – Osmium

In another answer, @RemusRusanu links to an alternative: 1) Create a certificate; 2) Create a user associated with that certificate; 3) Grant that user appropriate rights [to the protected resources]; and 4) Sign the sproc with the certificate, each time you change the sproc (https://mcmap.net/q/195727/-sql-server-permissions-on-stored-procs-with-dynamic-sql, linking to : sommarskog.se/grantperm.html#Certificates ). Signing adds the certificate user rights to the current user token, which SQL Server preserves when calling system procedures and dynamic SQL invoked through EXEC() or sp_executesql. So the sproc succeeds. – Spontaneity

Answer 1

Execute permissions on the stored procedure is sufficient.

CREATE TABLE dbo.Temp(n int)

GO
DENY INSERT ON dbo.Temp TO <your role>
GO
CREATE PROCEDURE dbo.SPTemp(@Int int)
AS

INSERT dbo.Temp
SELECT  @Int 

GO

GRANT EXEC ON dbo.SPTemp TO <your role>

GO

Then the (non-db_owner) user will have the following rights:

EXEC dbo.SPTemp 10
GO

INSERT dbo.Temp --INSERT permission was denied on the object 'Temp'
SELECT  10

However, if there is dynamic SQL inside dbo.SPTemp that attempts to insert into dbo.Temp then that will fail. In this case direct permission on the table will need to be granted.

Chickenlivered

This is at least misleading, if not wrong. Your example only works because the owner of the procedure and the owner of the table is the same, and ownership chaining skips the access check. If the owner of the procedure would not be the owner of the table, but simply have CONTROL permission on it (ie. has all permission, but is not the owner), your example would fail. – Atop

@Remu, one will need to be a member of the db_owner role for your scenario to pass. – Chickenlivered

In another answer, @RemusRusanu links to an alternative: 1) Create a certificate; 2) Create a user associated with that certificate; 3) Grant that user appropriate rights [to the protected resources]; and 4) Sign the sproc with the certificate, each time you change the sproc (https://mcmap.net/q/195727/-sql-server-permissions-on-stored-procs-with-dynamic-sql, linking to : sommarskog.se/grantperm.html#Certificates ). Signing adds the certificate user rights to the current user token, which SQL Server preserves when calling system procedures and dynamic SQL invoked through EXEC() or sp_executesql. So the sproc succeeds. – Spontaneity

Answer 2

Permissions on tables are not checked (including DENY) if tables and proc have the same owner. They can be in different schemas too as long as the schemas have the same owner.

See Ownership chaining on MSDN

Edit, from a comment from a deleted answer.

The context is always the current login unless EXECUTE AS as been used: only referenced object DML permissions are not checked. Try OBJECT_ID(referencedtable) in a stored proc where no rights are assigned to referencedtable. It gives NULL. If executed by the owner of the stored proc then it would give a value because owener has rights on referencedtable

Ledaledah

the OBJECT_ID hint was very helpful; I needed a way to check if a user is able to execute a stored procedure, this worked beautifully – Lune

Answer 3

Maybe you can use

"with execute as owner"

when you create the stored procedure, such as below:

create procedure XXX
with execute as owner
as
begin
...
end
go

Then you only need to grant the user the EXECUTE permission for the stored procedure XXX.

Dejected

this worked for me. was looking to provide access to a table to a user through only the stored procedure and this worked. the table does not have to be owned by the same schema as the stored procedure. – Subirrigate

(search terms) sp_executesql Azure SQL Server dynamic sql special permissions for dynamic sql on Sql Azure (end keywords). This seems to error in a pretty soft way on Azure SQL. You often have a different user context in SQL Azure and this detail is what you are looking for. – Expediential

Answer 4

Execute permission on a stored procedure that does an insert, update, or delete is sufficient. You do not need to grant those permissions at the table level. In fact, I would discourage that approach. Using a stored procedure gives you more control over how the change occurs. For instance, you may wish to do some checking prior to allowing the update. Using a stored procedure can also help prevent major accidents--like deleting all the rows in the table because someone forgot the WHERE clause!

Astigmatism

In this Case you need to look at yuan liu s answer above to accomplish your goals as you have stated. – Expediential

Answer 5

THANK YOU SO MUCH! I had a similar problem. This lead me to the answer.

I was attempting to trunctate a table in a stored procedure that called other stored procedures that were nested in IF statements.

My error was

The server principal "domain\my_id" is not able to access the database "2nd_DB" under the current security context.

I had given the calling stored procedure rights to do the truncate (EXECUTE AS SELF), which then caused a problem because SELF didn't have rights to the 2nd DB. Our solution was to move the truncate to another SP, include the EXECUTE AS SELF. We now call the truncate SP, execute our data processing, make logic determination, and call the appropriate 3rd SP.

Donothing