[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2567
  • Last Modified:

Permissions and SP_EXECUTESQL

I have a user in the database that has the following permissions:

public
test_role

test_role only has permissions to execute certain stored procedures. One of the procedures makes use of the SP_EXECUTESQL system sproc. When executing the sproc an error is generated that states that select permissions are denied on tables that are used in the query executed by SP_EXECUTESQL. When checking the db_owner role for that user the sproc works. The problem is I don't want to grant db_owner status to that user. How can I get around this permission problem and allow users that have only public and test_role permissions to make use of SP_EXECUTESQL?

Thanks.
0
wsteph
Asked:
wsteph
1 Solution
 
Scott PletcherSenior DBACommented:
That's one of the issues with dynamic SQL -- you must grant access directly to the tables.  

Assuming that the dynamic query(s) is(are) just reading, you could add SELECT authority to the table(s) to the test_role, like:
GRANT SELECT ON table1 TO test_role
You wouldn't have to give them full dbo authority, which I agree is probably not a good idea.

I don't think there's a way around this with dynamic SQL.  That's just the way permissions work.
0
 
wstephAuthor Commented:
Forgot about this - I could just set SELECT permissions on the tables involved for that user in EM.

Thanks.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now