Re: Exec statements in stored procedures

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 06/03/04 

Date: Wed, 2 Jun 2004 19:41:02 -0500

> Stored procedures execute in the context of the owner,
> dynamic sql executes in the security context of the current user.

Just to be clear, SQL Server bypasses permission checking on indirectly
referenced objects as long as the ownership chain is unbroken (i.e. all
objects involved are owned by the same user). This is not really the same
as running under the owner's security context.

Dynamic SQL accesses objects directly so permissions on those objects are
needed. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Andrew John" <aj@DELETEmistrose.com> wrote in message
news:u58XDxPSEHA.1764@TK2MSFTNGP10.phx.gbl...
> Jeremy,
>
> No. Stored procedures execute in the context of the owner,
> dynamic sql executes in the security context of the current user.
>
> This is (one of) the problems of using dynamic sql, which in
> your case you are only using because you want to pass a comma separated
list ?
> Cure MUCH worse than disease.
>
> The pros and cons of this have been pretty well discussed here.  Here is
one such discussion ( careful of word wrap ):
>
>
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=%23tKaKId2DHA.1736%40TK2MSFTNGP09.phx.gbl&rnum=2&prev=/groups%3Fq%3Dlist%2Bpassing%2Bdynamic%2Bgroup:microsoft.public.sqlserver.programming%2Bauthor:andrew%2Bauthor:john%26hl%3Den%26lr%3D%26ie%3DUTF-8%26as_drrb%3Db%26as_mind%3D12%26as_minm%3D5%26as_miny%3D2002%26as_maxd%3D2%26as_maxm%3D6%26as_maxy%3D2004%26selm%3D%2523tKaKId2DHA.1736%2540TK2MSFTNGP09.phx.gbl%26rnum%3D2
>
> A newsgroup search at google.com will give many more.
>
> Regards
>   AJ
>
>
> Jeremy Chapman wrote:
> > In my stored procedure I have to selects,  the first one in an exec, and
the
> > second one is just a standard exec.  The user only has exec access to
the
> > stored proc, no select access to tables.  The second select * from
> > CourseInstance works fine but the select in the exec errors out with a
> > selectpermission denied unless I put select rights for the user on the
> > CourseInstance table.  Is there any other way to do this, as I prefer to
> > only give rights to stored procedures.
> >
> > CREATE PROCEDURE dbo.pCR_ListCourseInstanceGroupsByID
@strCourseInstanceIDs
> > varchar(3000)
> > AS
> >
> > CREATE TABLE #TEMPIDS
> >    (
> >  [ID] int
> >    )
> >
> > IF LEN(@strCourseInstanceIDs) > 0
> > BEGIN
> >  EXEC
> >  ('
> >   INSERT INTO #TEMPIDS
> >   ([ID])
> >   SELECT
> >    [ID]
> >   FROM
> >    CourseInstance
> >   WHERE [ID] IN (' +  @strCourseInstanceIDs + ')'
> >  )
> > END
> >
> > SELECT * From CourseInstance
> >
> >

Relevant Pages

  • Re: SQL Injection Prevention
    ... > under the impression that all stored procedures contain dynamic SQL. ... more than 1.5 MLoC of Fortran code + more than 1.2 MLoC of C++ ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Injection Prevention
    ... > under the impression that all stored procedures contain dynamic SQL. ... more than 1.5 MLoC of Fortran code + more than 1.2 MLoC of C++ ...
    (microsoft.public.dotnet.security)
  • Re: Dynamic SQL
    ... Dynamic SQL versus stored procedures is really a minimal discussion. ... Obviously if you CAN use stored procedures, you are going to have a MUCH ... With only minimal modification you could make your middle tier use stored ... > While I am not trying to start another discussion about> business rules and where they ...
    (microsoft.public.sqlserver.programming)
  • Re: Confused about proc vs. dynamic SQL vs LINQ
    ... stored procedures are going to be faster than ... performance from dynamic sql (sql that you generate on the client and ... as well as if the queries are the same and a number ... another execution plan would be faster because statistics changed. ...
    (microsoft.public.dotnet.languages.csharp)
  • 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)