Solved

Permissions and SP_EXECUTESQL

Posted on 2002-03-23
2
2,455 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

636 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