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:
CREATE #tmpData ( [some_fields] )
and then it uses dynamic SQL to populate the data
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!
CatDynamic 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.|||Try this ... put your insert before your exec ...


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|||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|||Your user is going to have to have select permissions on tData.|||Excellent suggestion.
You'll need to grant READ permission to the user on table tData.|||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|||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.|||Excellent suggestion.

Good advice is always worth repeating :cool:|||You could kludge your way around part of the problem using something like: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|||If security is a big issue,

I have to go change my pants now

"If"...good lord