2

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.

Improve this question

2 Answers 2

Reset to default
3

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.

Improve this answer
1
  • Thanks! On the internet I was reading about signing certificates and other overkill. All I needed was to add a login with just the db_datareader database-role. Now I can have my asp.net login run sprocs that use sp_executesql by adding "WITH EXECUTE AS 'DataReaderOnlyUser'" to the sproc definition.
    – MikeTeeVee
    Commented Oct 7, 2011 at 21:45
0

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' ]
Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.