Executing Dynamic SQL with out Select Permission

I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute sp_executesq
l
@.sql I am executing the dynamic sql, few of my procedures are getting input
parameter for table name and/or column names also. Now the database user is
modified with privileges, he has assigned only execute Permission. How to
solve this problem.You can't. If you use dynamic SQL you need permissions on the underlying
tables. Passing in table names and column names as parameters to a stored
procedure is not a good idea anyway, and the problem you have run into is
only one of the issues (see http://www.sommarskog.se/dynamic_sql.html). If
you can explain what you are actually trying to do, someone here can come up
with a better solution.
Jacco Schalkwijk
SQL Server MVP
"Prakash" <Prakash@.discussions.microsoft.com> wrote in message
news:F99F00EB-1F62-4CD4-8E66-A299A6192480@.microsoft.com...
>I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute
>sp_executesql
> @.sql I am executing the dynamic sql, few of my procedures are getting
> input
> parameter for table name and/or column names also. Now the database user
> is
> modified with privileges, he has assigned only execute Permission. How to
> solve this problem.|||Unfortunatly you can't, with dynamic SQL you must have Select Permission on
the table.
If you tell us what you are tryng to do we could possible sugest an
alternative.
Peter
Do not arouse the sleeping dragon, for you are crunchy and taste good with
ketchup.
"Prakash" wrote:

> I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute sp_execute
sql
> @.sql I am executing the dynamic sql, few of my procedures are getting inpu
t
> parameter for table name and/or column names also. Now the database user i
s
> modified with privileges, he has assigned only execute Permission. How to
> solve this problem.|||> I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute
sp_executesql
> @.sql I am executing the dynamic sql, few of my procedures are getting inpu
t
> parameter for table name and/or column names also.
Care to explain just WHY you are doing that? The usual reasons are poor
database design and/or poor coding practices. The solution is almost always
not to do it. Dynamic SQL comes with a lot of incovenient baggage: security
vulnerabilities; performance implications; maintenance and reliability
issues; cost to develop and support.
David Portas
SQL Server MVP
--
"Prakash" wrote:

> I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute sp_execute
sql
> @.sql I am executing the dynamic sql, few of my procedures are getting inpu
t
> parameter for table name and/or column names also. Now the database user i
s
> modified with privileges, he has assigned only execute Permission. How to
> solve this problem.|||I don't know if this applies in your case, but it helped avoid dynamic SQL
on a project of mine. If you need to query across multiple partitioned
tables (ex: SALES_2004, SALES_2003, etc), then consider using a partitioned
view (basically a view of unionized tables). When a new table is added, then
you can re-create the view that includes the new table reference.
"Prakash" <Prakash@.discussions.microsoft.com> wrote in message
news:F99F00EB-1F62-4CD4-8E66-A299A6192480@.microsoft.com...
> I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute
sp_executesql
> @.sql I am executing the dynamic sql, few of my procedures are getting
input
> parameter for table name and/or column names also. Now the database user
is
> modified with privileges, he has assigned only execute Permission. How to
> solve this problem.