Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sp_executesql , select permission Denied

Posted on 2014-07-26
1
Medium Priority
?
731 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 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

     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 …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

715 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