dynamic sql runs under which user ?

Question

dynamic sql runs under which user ?

SteveB-74825

Hall of Fame

Points: 3290
More actions
January 30, 2004 at 2:54 am

#107611

hi all,
just got back from a week in germany and am fixing the stuff my boss created whilst i was away (he had promised he would touch nothing )
he has created a database that has dynamic table creation and dymanic sql (he's a programmer by nature)
the solution needs neither but as i was messing around with it i noticed the following whihc puzzled me.
he has a stored procedure that builds sql and then EXECs it. the stored procedure was created by dbo and accesses tables created by dbo. execute permission was granted for the SP to the access user who has no permissions granted on the tables themselves. and we got 'user does not have select permission on dbo.blah'.
however a straight sql query (not built dynamically) works fine (execute permission only)
does dynamic SQL run under a different 'user' ?
should we be using sp_executesql as a matter of course ?
any ideas ?
TIA
Steve

cheers
dbgeezer

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

No, dynamic sql is executed with permissions of the current user, even if in stored procedures.

Yes, if you need to use dynamic sql, you should use sp_executesql. It allows for parameters and gives you a better chance that SQL Server will reuse query plans.

For the sake of completeness, take a look at this one:

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

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

SteveB-74825 Hall of Fame Points: 3290 More actions · Answer 2

okay - still a bit confused though

working on the assumption that i am sa.

if i write a stored procedure to run under user A that accesses tables created by me where i have granted execute permissions to user A do i have to set table level permissions for user A on all the tables the stored procedure references ?

i thought that granting execute on the SP allowed the user to select from the tables without having select permisssions for the table ...

Steve

cheers

dbgeezer

SteveB-74825 Hall of Fame Points: 3290 More actions · Answer 3

next time i'll read the whole article !

<snip>

If you cannot give users direct access to the tables, you cannot use dynamic SQL, it is as simple as that. In some environments, you may assume that users can be given SELECT access. But unless you know for a fact that permissions is not an issue, don't use dynamic SQL for INSERT, UPDATE and DELETE statements. I should hasten to add this applies to permanent tables. If you are only accessing temp tables, there are never any permission issues.

</snip>

cheers

dbgeezer

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 4

Anytime you use EXECUTE or sp_executesql, this is the case. The problem that results is that with the use of these two methods, the new query executes in a separate batch process and therefore security is rechecked immediately.

K. Brian Kelley
@kbriankelley