Re: Exec statements in stored procedures

From: Jeremy Chapman (NoSpam_at_Please.com)
Date: 06/03/04 

Date: Thu, 3 Jun 2004 09:23:46 -0700

Yes, I'm only using dynamic sql because of the comma seperated list. I
suppose I could parse the list out and do insert statements in a while
loop...

"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: Exec statements in stored procedures
    ... No. Stored procedures execute in the context of the owner, ... dynamic sql executes in the security context of the current user. ... > second one is just a standard exec. ... > CourseInstance table. ...
    (microsoft.public.sqlserver.programming)
  • 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: Exec statements in stored procedures
    ... > dynamic sql executes in the security context of the current user. ... Dynamic SQL accesses objects directly so permissions on those objects are ... > No. Stored procedures execute in the context of the owner, ...
    (microsoft.public.sqlserver.programming)