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

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 Snippet

CREATE #tmpData ( [some_fields] )

and then it uses dynamic SQL to populate the data

Code Snippet

SELECT @.ExecStr =
'INSERT INTO #tmpData
SELECT * FROM tData
WHERE [some_condition]
ORDER BY ' + @.SortColumn /* input parm to the SP */
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

If you are using SQL 2000, you have no choice. To use dynamic SQL requires a high level of permissions.

IF you are using SQL 2005, explore the 'EXECUTE AS' property.

Refer to Books Online, Topic: 'EXECUTE AS'

Permissions

Hi All
I would like to run a job that checks for any stored procedures without
PUBLIC access and grant them public access. What would be the best way to d
o
this and which tables/sp do i have to use? Thank you in advance.If you are using SQL Server 2005 you can do that easily with only this
command:
grant execute on database::mydb to public
Ben Nevarez, MCDBA, OCP
Database Administrator
"Baldy" wrote:

> Hi All
> I would like to run a job that checks for any stored procedures without
> PUBLIC access and grant them public access. What would be the best way to
do
> this and which tables/sp do i have to use? Thank you in advance.