EXEC in stored procedure

Hello!
I'm trying to use
EXEC ('SELECT * FROM Categories')
into a Stored Procedure, but the users get permission denied on object
'Categories'
If I'm using
SELECT * FROM Categories
everything works.
The user haven't any permission of Categories, but the SP should have it.
Is the EXEC command inside the SP run as the user? Why?
Regards MagnusHi Magnus
The dynamic select doesn't obey the same ownership chaining rules as
statements outside the EXEC. Users running the stored procedure must have
explicit permissions on the objects in the dynamic SQL.
A SELECT outside of the EXEC obey ownerships chaining rules, and any user
who has execute permission on procedure can run the statements in the
procedure, as long as the procedure and the table have the same owner.
Why do you need the EXEC? It serves no purpose here.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Magnus Blomberg" <magnus.blomberg@.skanska.se> wrote in message
news:utP4%2373uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I'm trying to use
> EXEC ('SELECT * FROM Categories')
> into a Stored Procedure, but the users get permission denied on object
> 'Categories'
> If I'm using
> SELECT * FROM Categories
> everything works.
> The user haven't any permission of Categories, but the SP should have it.
> Is the EXEC command inside the SP run as the user? Why?
> Regards Magnus
>|||When you use:
EXEC ('SELECT * FROM Categories')
... you are using dynamic SQL. Thus, the person running it must have their
permissions checked against the underlying objects - Categories, in this
case.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Magnus Blomberg" <magnus.blomberg@.skanska.se> wrote in message
news:utP4%2373uEHA.4084@.TK2MSFTNGP10.phx.gbl...
Hello!
I'm trying to use
EXEC ('SELECT * FROM Categories')
into a Stored Procedure, but the users get permission denied on object
'Categories'
If I'm using
SELECT * FROM Categories
everything works.
The user haven't any permission of Categories, but the SP should have it.
Is the EXEC command inside the SP run as the user? Why?
Regards Magnus|||> Is the EXEC command inside the SP run as the user? Why?
One reason I can think of is because dynamic SQL is a very serious security
concern, and if you allow any table name -- or any SQL command, for that
matter -- to run, just because the user can run the stored procedure, then a
user can wreak havoc on your system quite easily.
So, the engine verifies permission once it resolves the dynamic SQL...
A|||Hi all of you!
Well, I thought I should get that question. This message is written at home
without any SQL, so it might be small code errors.
First I can tell you, this SP doesn't accept any input parameters, so I
think the security riscs are quite small.
I must also say, that the system is rather old and is using application
role, so the objects aren't made for any user access for the users.
The reason for using dynamic SQL is that the SP will collect info about
tables into table #tbls as TableName, IDColumn, ValueColumn etc.
Then this is used by a Cursor to add data from the tables specified in #tbls
to table #rows.
Eg: INSERT #tbl (@.IDColumn,@.ValueColumn) INTO #rows FROM @.TableName
Correct me if I'm wrong but the row above is not valid, because it's not
using dynamic SQL, yea?!?!
Then the SP should return as SELECT * FROM #rows
This is the complete purpose, and I found this very difficult not using
EXEC...
Any ideas how to do the similar without using dynamic SQL.
Regards Magnus
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OgfAhO4uEHA.3456@.TK2MSFTNGP14.phx.gbl...
> One reason I can think of is because dynamic SQL is a very serious
security
> concern, and if you allow any table name -- or any SQL command, for that
> matter -- to run, just because the user can run the stored procedure, then
a
> user can wreak havoc on your system quite easily.
> So, the engine verifies permission once it resolves the dynamic SQL...
> A
>