Public Permissions - SQL Server Forums

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

SQL Server Development (2000)

Public Permissions

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-10-31 : 09:41:09

I've got a SP that builds a query in a string and executes it using

exec sp_executesql string

When I call this procedure using ASP/ADO with a DB role of 'Public', the procedure executes, but I get denials on the tables like

SELECT permission denied on object 'Centers', database 'xyz2003', owner 'dbo'.
Server: Msg 229, Level 14, State 1, Line 1

I don't have these select permission problems in other SPs that do not use dynamic SQL.

It will be difficult to replace this query without dynamic sql. Is there an alternative without granting SELECT permission to the role Public for each table?

Sam

Max_rv
Starting Member

3 Posts

Posted - 2002-10-31 : 09:47:57

Try this:
exec (@QueryString)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-31 : 09:48:28

Can you post the proceudre code, and a description of what the procedure does? There might be a way to do it with dynamic SQL.

SamC
White Water Yakist

3467 Posts

Posted - 2002-10-31 : 12:49:50

Thank you both for your help.

That SP was so ugly

I couldn't post it for other eyes.

I've had to reduce the functionality a bit, and rewritten the SP without using the EXEC.

Here's a couple of lessons ( for me anyway )

I could not get the Public Role to execute a SP with "EXEC string" correctly. I never found out why, but I don't have the problem any longer.

Having rewritten the SP using dynamic WHERE and ORDER BY:

There are two *almost* equivalent ways to write dynamic WHERE. Despite my preference to the contrary in
[url]http://sqlteam.com/forums/topic.asp?TOPIC_ID=6003[/url]

WHERE columna = IsNull(@columna, columna)

is inferior to

WHERE (@columna IS NULL OR columna = @columna)

in the instance where (@columna=NULL), and the value in columna *IS NULL* the former WHERE fails, where the latter WHERE works just fine.

Sam

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-10-31 : 16:18:13

To better understand why dynamic SQL requires different permissions on tables referenced than for example a store procedure that you give exec permissions on , have a look at
[url]http://www.algonet.se/~sommar/dynamic_sql.html[/url]

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources