Solved

Permissions and SP_EXECUTESQL

Posted on 2002-03-23
2
2,400 Views
Last Modified: 2012-05-04
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
Comment
Question by:wsteph
2 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 50 total points
ID: 6894052
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
 

Author Comment

by:wsteph
ID: 6895066
Forgot about this - I could just set SELECT permissions on the tables involved for that user in EM.

Thanks.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question