Sql-server – Stored Procedure and Permissions – Is EXECUTE enough

permissionssql serverstored-procedures

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".

Best Answer

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

Related Solutions

Sql-server – Select columns from result set of stored procedure

Can you split up the query? Insert the stored proc results into a table variable or a temp table. Then, select the 2 columns from the table variable.

Declare @tablevar table(col1 col1Type,..
insert into @tablevar(col1,..) exec MyStoredProc 'param1', 'param2'

SELECT col1, col2 FROM @tablevar

Sql-server – SQL Server: Query fast, but slow from procedure

I had the same problem as the original poster but the quoted answer did not solve the problem for me. The query still ran really slow from a stored procedure.

I found another answer here "Parameter Sniffing", Thanks Omnibuzz. Boils down to using "local Variables" in your stored procedure queries, but read the original for more understanding, it's a great write up. e.g.

Slow way:

CREATE PROCEDURE GetOrderForCustomers(@CustID varchar(20))
AS
BEGIN
    SELECT * 
    FROM orders
    WHERE customerid = @CustID
END

Fast way:

CREATE PROCEDURE GetOrderForCustomersWithoutPS(@CustID varchar(20))
AS
BEGIN
    DECLARE @LocCustID varchar(20)
    SET @LocCustID = @CustID

    SELECT * 
    FROM orders
    WHERE customerid = @LocCustID
END

Hope this helps somebody else, doing this reduced my execution time from 5+ minutes to about 6-7 seconds.

Best Answer

Related Solutions

Sql-server – Select columns from result set of stored procedure

Sql-server – SQL Server: Query fast, but slow from procedure

Related Topic