SELECT permission was denied on the object

Question

If have created a custom role within SqlServer which I added to the db__denydatareader and db__denydatawriter roles. I think went through the db and granted exec permission to all neccersary stored procedures.

Everything works fine, calling those sps will run fine. The one exception is a stored procedure which executes dynamic sql by using sp_executesql. This fails saying

The SELECT permission was denied on the object 'listing_counter', database 'Cannla', schema 'dbo'.

Is there any way to grant the role permission to run this query without giving it select access to the underlying tables?

I guess what I'm wanting to do is grant exec on sys.sp_executesql but only in a certain case.

Answer 1

Use using the following statement for that . It worked for me.

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] [ , [ @provider= ] 'provider_name' ]

 [ , [ @datasrc= ] 'data_source' ] 
 [ , [ @location= ] 'location' ] 
 [ , [ @provstr= ] 'provider_string' ] 
 [ , [ @catalog= ] 'catalog' ]

Answer 2

You can create a new user just for your stored procedure that uses execute_sql, grant him the requires rights and then add to the procedure definition WITH EXECUTE AS 'MyUser'. See MSDN.