Solved

Permissions and SP_EXECUTESQL

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

770 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