Solved

sp_executesql , select permission Denied

Posted on 2014-07-26
1
657 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 500 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

776 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