Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    125

    Unanswered: dynamic sql and rights issues

    Ok, I finally got the company working towards having a new log in for our app that will only have the exec rights of the Stored Procs the app uses. The problem is we came up with an issue using dynamic sql. Seems that SQL2k wants to have select rights to the table you are using to select from when generating a dynamic string within the stored procedure:

    create spTestDynamic
    as
    declare @test varchar(100)

    set @test = 'Select * from table'

    exec (@test)
    --or
    --exec sp_executesql @test


    Both exec statements want to have select rights to the table. Is there a way around this or am I screwed and have to rewrite these stored procs so that it doesn't use dynamic sql?

    Thanks,
    DMW

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You should avoid dynamic sql anyway

    http://www.sql-server-performance.co...procedures.asp

    Why do you need to use it?

    Got an example?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2004
    Posts
    125
    We have a search procedure to search our customers and with in the stored procedure we build the where clause. Something like:

    if len(@fname) > 0
    begin
    set @where = @where + ' fname = ' + @fname
    end
    if len(@lname) > 0
    begin
    set @where = @where + ' lname= ' + @lname
    end

    set @query = 'Select....from table ' + @where

    exec @query

    This is within the stored procedure and not the code on the app. Is there a better way to do this?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I think so...

    I like this and the comments that follow

    http://weblogs.sqlteam.com/brettk/ar...5/05/1312.aspx
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •