Re: Exec permissions on Stored Procedure

Mark B (mark_bert@xxxxxxxxx) writes:
On Aug 5, 11:14 am, "gv" <viator.ge...@xxxxxxxxx> wrote:
If I grant EXEC permissions on a Stored Procedure to a Domain User
shouldn't they be able to Execute that stored procedure reguardless if
they have Select permissions on a table that the Stored Procedure uses?

Interesting timing. I was just about to make a post concerning the
same thing. On my SQL2000 system (not sure which sp), If I grant
execute permission on a stored procedure, that's all that needs to be
done. I'm working on another system that has SQO2005 and it's
requiring me to also grant select permission to the underlying table.
This seems like a gaping security hole to me. I hope someone out there
has an answer for this.

No, it's not a security hole, it's that you probably have not grasped
the mechanism wherby a user gets access to objects through stored
procedures.

We often think of it as "if a users the rights to execute a stored
procedure, he does not need access to the underlying table", but there is
a very important assumption which is tacitly understood: the table and
the procedure have the same owner. The mechanism is therefore also known
as *ownership chaining*.

And, oh, there is another important thing as well: ownership chaining
does not apply to dynamic SQL, because dynamic SQL is not part of the
procedure that invokes it, and the dynamic SQL has no owner, so there
can be no ownership chaining.

If you want more information on ownership chaining and other
permission mechanisms, this article on my web site may be of
interest: http://www.sommarskog.se/grantperm.html.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Execute stored procedure only
    ... You need CONNECT capability to be able to get into the database and execute ... You can compare this to having permission to eat a piece of pie, ... Should I not be able to just execute SP through QA? ... right to execute stored procedure in database called "mydatabase". ...
    (microsoft.public.sqlserver.security)
  • Re: Logging within User_Defined Functions (UDF)
    ... You can't perform dynamic SQL or DML in a function. ... Looks like a stored procedure that you ... Ultimately you can only execute ... > DECLARE @cmdstr nvarchar ...
    (microsoft.public.sqlserver.programming)
  • Re: EXECUTE permission denied on object... uh?
    ... connecting with doesn't have permission to execute the Stored Procedure ... runBehavior, SqlCommand cmdHandler, SqlDataReader ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: stored procedure security
    ... > execute privelages on the stored procedure, ... As soon as you employ dynamic sql, the logon executing the stored procedure ... In order to execute this stored ...
    (microsoft.public.sqlserver.security)
  • Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string
    ... Once you go to dynamic SQL, you are outside the ownership / security context ... I wrote a script that GRANTS EXECUTE to ... every stored procedure in the database to and DENY ...
    (microsoft.public.sqlserver.security)