March 10, 2016 at 4:51 pm
Hi Everyone.
We have a stored procedure called SP1. This SP is being called by user1. Since User1 does not have direct access to table1 in database1, so we have to execute the SP by 'with execute' option using a user2 who has read access to table1
Below is the definition of SP1 .
Use database1
go
alter procedure SP1
@TableName as varchar( 50)
with execute as User2
as
declare @SqlStr varchar (200 )
select
@SqlStr = " select * from database1.dbo.table1 t1 inner join " + @TableName + " t2 on t1.code = t2.code"
exec sp_executesql @SqlStr
go
when I ran exec SP1 'tempdb.User1.table2' , I faced with below error message.
The SELECT permission was denied on the object 'table2', database 'tempdb', schema 'User1'.
User2 has read access to Table1 in database1 and also I have given select permission on User1 schema on tempdb. But still it is complaining about select permission on table2.
I would appreciate if someone helps me on this issue.
Thanks
March 11, 2016 at 1:20 pm
Throw this in there to help you troubleshoot:
SELECT SUSER_NAME(), USER_NAME();
March 11, 2016 at 6:47 pm
How can this help for troubleshooting?
March 15, 2016 at 10:50 am
Since I replied on the other thread for this issue because I never saw this one, I'll reply here.
The permissions issue is going to be a problem because sp_executesql creates a new execution context. You should still be able to get it to work using EXECUTE AS, but, if you change database context as well, then EXECUTE as USER won't work, I believe you'll need EXECUTE AS LOGIN.
That isn't your biggest issue. The biggest issue is the dynamic sql passing in a table name, there is no reason why you should, in this day, write code that is so open to SQL Injection. I don't know what your requirements are, but I can say that there is probably a more secure way to accomplish your goal.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply