Rights running Exec (SQL)

Hi all.
It looks like I need READ rights on the involved tables using Exec (SQL)
within a prosedure instead of SELECT TableName and so on.It is not enough to
have EXEC rights on the prosedure itself. Is there any way around this or do
I need go grant read rights on all the tables in the SQL string.
If so, is there a easy way within the prosedure to check if the user have
this rights and if not grant it on the fly?
Thanx all.
gh> It looks like I need READ rights on the involved tables using Exec (SQL) within a prosedu
re
> instead of SELECT TableName and so on.
I assume that you wanted to say that you need SELECT permissions on the tabl
e, that EXEC procname
isn't enough. Yes, this is one of the drawbacks of using dynamic SQL. First
step is to see if you
really need that dynamic SQL. Then see what the new features in 2005 can do
for you:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/grantperm.html
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geir Holme" <geir@.multicase.no> wrote in message news:%23YXZ7i0mGHA.3928@.TK2MSFTNGP03.phx.
gbl...
> Hi all.
> It looks like I need READ rights on the involved tables using Exec (SQL) w
ithin a prosedure
> instead of SELECT TableName and so on.It is not enough to have EXEC rights
on the prosedure
> itself. Is there any way around this or do I need go grant read rights on
all the tables in the
> SQL string.
> If so, is there a easy way within the prosedure to check if the user have
this rights and if not
> grant it on the fly?
> Thanx all.
> gh
>|||Hi Tibor.
Thanx for the document. We are close to SQL2005, but not yet there.
I ended up with creating av view with the tables in question and runned the
EXEC on this view. That worked and the tables are still "safe" and hidden
for this user.
Thank you again for your interest in this.
-gh
"Geir Holme" <geir@.multicase.no> wrote in message
news:%23YXZ7i0mGHA.3928@.TK2MSFTNGP03.phx.gbl...
> Hi all.
> It looks like I need READ rights on the involved tables using Exec (SQL)
> within a prosedure instead of SELECT TableName and so on.It is not enough
> to have EXEC rights on the prosedure itself. Is there any way around this
> or do I need go grant read rights on all the tables in the SQL string.
> If so, is there a easy way within the prosedure to check if the user have
> this rights and if not grant it on the fly?
> Thanx all.
> gh
>