Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2005
    Posts
    45

    Unanswered: Permissions Problem using Dynamic SQL

    Hi all!

    I've got a problem where I have created a stored procedure (using MS SQL Server 2000) that does a temporary table creation:
    Code:
    CREATE #tmpData ( [some_fields] )
    and then it uses dynamic SQL to populate the data
    Code:
    SELECT @ExecStr = 
    	'INSERT INTO #tmpData
    	SELECT * FROM tData
    	WHERE [some_condition]
    	ORDER BY ' + @SortColumn  /* input parm to the stored proc */
    EXEC (@ExecStr)
    I get a permissions error 229 when I try to run this because my user only has execute permissions for the stored procedure within the database. The only thing that I've found so far that will fix this is if I change the user's permissions to db_owner, which I don't want to do.

    I've tried to explicitly grant permission within the stored proc, but since the object (the temp table) does not actually reside in the database, that gives me an error as well (4610: You can only grant or revoke permissions on objects in the current database.).

    Is there anything else I can do? I really don't want to have to give the user that much freedom within the database, and removing the dynamic SQL really isn't a viable option either.

    Thanks in advance for your help!
    Cat
    Last edited by catdavis67; 06-19-07 at 10:24.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Dynamic SQL executes within its own scope, not that of the stored procedure. So it does not inherit the stored procedures rights, and only operates under the connection's rights.
    You'll need to grant READ permission to the user on table tData.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Try this ... put your insert before your exec ...

    Code:
     
    SET NOCOUNT ON
    USE master
     
    create table #temp (dbname sysname)
     
    declare @sql nvarchar(255)
    select @sql = 'select name from sysdatabases'
     
    insert into #temp
    exec sp_executesql @sql
     
    select * from #temp
     
    drop table #temp

    -- This is all just a Figment of my Imagination --

  4. #4
    Join Date
    Jun 2005
    Posts
    45
    Thanks, Tom!

    That was a good idea. Unfortunately it still gives me the same error.

    Thanks again! If you have any other ideas, let me know.
    Cat

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Your user is going to have to have select permissions on tData.

    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Excellent suggestion.
    Quote Originally Posted by blindman
    You'll need to grant READ permission to the user on table tData.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jun 2005
    Posts
    45
    Thanks everyone!

    I did get this running with just the SELECT permissions on the data tables involved. Although this is still not ideal from a security perspective, it is better than having to give them db_owner. I appreciate the feedback!

    Thanks again,
    Cat

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If security is a big issue, you could create a view based upon the table showing only the required columns and filtered rows, and then reference the view in your dynamic SQL. Then you can grant permissions on the view rather than on the table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by blindman
    Excellent suggestion.
    Good advice is always worth repeating

    -- This is all just a Figment of my Imagination --

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You could kludge your way around part of the problem using something like:
    Code:
    DECLARE @i		INT
    
    SET @i = 1
    
    SELECT o.name
       FROM dbo.sysobjects AS o
       ORDER BY
          CASE @i
             WHEN 1 THEN o.name
             WHEN 2 THEN Str(o.id, 20)
             ELSE Convert(CHAR(30), crdate, 121)
          END
    -PatP

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    If security is a big issue,

    I have to go change my pants now

    "If"...good lord
    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
  •