Re: broken ownership chains

From: BP Margolin (bpmargo@attglobal.net)
Date: 10/11/02 

From: "BP Margolin" <bpmargo@attglobal.net>
Date: Fri, 11 Oct 2002 14:20:21 -0400

Sarathy,

This behavior has nothing to do with broken ownership chains. Rather,
dynamic SQL requires that the user calling the stored procedure have not
only EXEC permissions on the stored procedure, but appropriate permissions
on all the database objects referenced within the dynamic SQL. This is
documented in the SQL Server Books Online:

"Permissions to use the statement(s) within the EXECUTE string are checked
at the time EXECUTE is encountered, even if the EXECUTE statement is
included within a stored procedure. When a stored procedure is run that
executes a string, permissions are checked in the context of the user who
executes the procedure, not in the context of the user who created the
procedure."

You are "shooting yourself in the foot" by using dynamic SQL and denying
access to the table referenced in the dynamic SQL.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"sarathy" <get_Sarathy@yahoo.com> wrote in message
news:24f901c27146$93c86b30$3aef2ecf@TKMSFTNGXA09...
> I have three objects with the same owner
> 1 table and 2 SPs
> both the SPs do a select * from the table -
> one directly and another dynamically
>
> I give exec permission to both SPs to an user and deny
> select on the table
>
> Now when the user executes the dynamic SP, the ownership
> chain seems to be broken . why is this so?
> eg:
>
> CREATE TABLE
> OnlyNumbers
> (
> a int
> )
> go
>
> insert into OnlyNumbers values (1)
> insert into OnlyNumbers values (2)
> insert into OnlyNumbers values (3)
> go
>
> go
> SELECT * from OnlyNumbers
>
> go
> CREATE PROC
> direct
> as
> SELECT * from OnlyNumbers
> go
> CREATE PROC
> dynamicProc
> as
> EXEC('SELECT * from OnlyNumbers')
>
> go
> GRANT EXECUTE
> ON direct
> TO <some user>
>
> go
> GRANT EXECUTE
> ON dynamicProc
> TO <some user>
>
> go
>
> DENY SELECT
> ON OnlyNumbers
> to <some user>
>
> Thanks
> Sarathy

Relevant Pages

  • Re: Cannot perform BULK INSERT even though the account is a bulkadmin
    ... When you use dynamic sql, all object permissions are checked ... BULK INSERT command seems to be setting some SET option ... > We have a stored procedure that dynamically constructs a BULK INSERT ...
    (microsoft.public.sqlserver.security)
  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... stored procedure, the user needs EXECUTE rights to the stored procedure. ... Query 1 requires the user to have SELECT rights on the Customers ... Also, FWIW, the dynamic SQL is only syntax checked at run time, not when the ...
    (microsoft.public.sqlserver.tools)
  • Re: Pass-thru SQL performance vs Stored Proc
    ... >> are going to have to create a lot of dynamic SQL based on user selects ... > stored procedure per permutation of parameters, ... > ms per execution, and you are executing it 100 times an hour, no problem, a ... Right now all our code is in stored procs. ...
    (microsoft.public.sqlserver.programming)
  • 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)