Solved

Permissions and SP_EXECUTESQL

Posted on 2002-03-23
2
2,376 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Upgrading my SSIS package in VS 2012 6 59
First Max value 3 27
install report service in sccm2012 3 18
SQL - Simple Pivot query 8 13
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

839 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