?
Solved

sp_executesql , select permission Denied

Posted on 2014-07-26
1
Medium Priority
?
782 Views
Last Modified: 2014-07-31
Hello
I have table myTable(col1,col2,col3)
I denied the permmissions o this table as follows
DEny Update,delete,select,insert on myTable TO EndUser1

I created PROC
Create proc MyProc
AS
Declare @SqlStatement varchar(50)
Set @SqlStatement ='Select col1,col2,col3 From myTable'
sp_executesql
--
Grant Execute on MyProc  to Enduser1
----
When EndUser1 exec MyProc  
an error message comes up
The SELECT permission was denied on the object 'myTable',

--
I had to add  
WITH EXECUTE AS OWNER to the procedure
My Question is there another solution let me   to grant execute Proc. to the enduser1 without using WITH EXECUTE AS OWNER?
0
Comment
Question by:ali_alannah
1 Comment
 
LVL 16

Accepted Solution

by:
Easwaran Paramasivam earned 1000 total points
ID: 40221574
You are trying to execute dynamic SQL within your stored procedure. What you need to remember is that when you do this it does not get executed within the context of the stored procedure - it gets executed within a new session. Because of this, the fact that the statement is being called within a stored procedure is a mute point and you will need to grant explicit permission on the objects that your dynamic SQL is using. If you don't want to do this I would refactor your stored procedure to not use dynamic SQL.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 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