Permissions Problem using Dynamic SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Permissions Problem using Dynamic SQL

Author

Topic

catdavis67
Starting Member

6 Posts

Posted - 2007-06-19 : 09:39:26

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] )[/CODE]
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)[/CODE]
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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-19 : 09:59:31

Congratulations! you have uncovered one of the many reasons NOT to use dynamic sql: in addition to making things harder to read, write and work with, and not being compiled ahead of time so that you can verify that table and column names exist, and being open to sql injection, you also have permissions issues like you are encountering. In short, dynamic SQL should rarely, if ever, be used.

So ... why do you feel you need dynamic sql? This is easily implemented with a simple conditional sort: http://www.sqlteam.com/article/dynamic-order-by

(be sure to read the comments)

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-19 : 10:01:20

also, be sure to read this:

http://www.sommarskog.se/dynamic_sql.html

- Jeff
http://weblogs.sqlteam.com/JeffS

catdavis67
Starting Member

6 Posts

Posted - 2007-06-19 : 10:35:31

Hi Jeff,
Thanks for your response, I'll be sure to read the articles you've submitted. In my defense, I'd like to say that I am probably as against dynamic SQL as you are. The problem mentioned above is greatly simplified from what the actual Dynamic SQL is. I use dynamic variables in other parts of the statement as well. Also, my "order by" could potentially have 185 different values to sort by and I'd really rather not have to type all that out and then maintain it when things change. Here is some of the actual SQL:

-- Create the temporary table used for determining sort order thru RowID
CREATE TABLE #tmpPerf970Cache (
RowID int IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
Perf970DataID int NULL, --This is used for detail view, all other columns are used for summary
GroupCode varchar(10) NULL,
SortValue varchar(100) NULL,
SummaryResultValue1 varchar(100) NULL,
SummaryResultValue2 varchar(100) NULL,
SummaryResultValue3 varchar(100) NULL,
SummaryResultValue4 varchar(100) NULL,
SummaryResultValue5 varchar(100) NULL )

-- Populate the temp table to generate the row IDs for the cache
IF @IsSummary = 1
BEGIN
SELECT @ExecStr = 'INSERT INTO #tmpPerf970Cache
SELECT DISTINCT NULL, ' + @GroupColumn + ',
' + @SortColumn + ',
' + IsNull(@SummaryResultColumn1,'NULL') + ',
' + IsNull(@SummaryResultColumn2,'NULL') + ',
' + IsNull(@SummaryResultColumn3,'NULL') + ',
' + IsNull(@SummaryResultColumn4,'NULL') + ',
' + IsNull(@SummaryResultColumn5,'NULL') + '
FROM tPerf970Data d
INNER JOIN tPerf970Cache c ON d.Perf970DataID = c.Perf970DataID
INNER JOIN tLUAccountType act ON d.AccountTypeCode = act.AccountTypeCode
INNER JOIN tLUAcctSubType asb ON d.AcctSubTypeCode = asb.AcctSubTypeCode
INNER JOIN tLUServiceLevel svl ON d.ServiceLevel = svl.ServiceLevelID
INNER JOIN tAccount a ON d.AccountID = a.AccountID
LEFT OUTER JOIN tHFPUSLICInfo h
ON Right(''00000'' + d.ConsigneePostalCode, 5) = h.PostalCode
AND d.ConsigneeCountryCode = h.CountryCode
WHERE (c.UserID = ' + Convert(varchar(10),@UserID) + '
AND c.CompanyID = ' + Convert(varchar(10),@CompanyID) + '
AND c.QueryID = ' + Convert(varchar(10),@NewQueryID) + ')
ORDER BY ' + @SortColumn + @SortDescStr
EXEC (@ExecStr)
END

(Sorry about the formatting, I can seem to get the post to format right.) This temporary table is used to generate the row ID so that I can manage paging and sorting. Some of the variables are passed in as input parameters and some are obtained from a table based on which summary "drill down" screen we are on (for grouping).

I'll look at your articles, however, and see if it's a viable solution. If you can think of any way to make the dynamic SQL option work, I'd really appreciate further feedback.

Thanks again,
Cat

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources