• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 856
  • Last Modified:

sp_executesql , select permission Denied

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
ali_alannah
Asked:
ali_alannah
1 Solution
 
Easwaran ParamasivamCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Tackle projects and never again get stuck behind a technical roadblock.
Join Now