Solved

sp_executesql , select permission Denied

Posted on 2014-07-26
1
706 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
[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
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

631 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