October 23, 2013 at 7:31 am
I am having issues on using stored procedure during SSIS package run. I have created stored procedure and package, its runs fine if I am running the package on my machine, but it gives "access dined on SP" when someone else run same package on their machine. we both are on same domain and the database. I believe we both has same level of permission. so I wasn't sure what should be done from me into Stored Procedure so other can use the same. I tried to give EXECUATE AS CALLER but it still doesn't work. Can someone explain me what should be done either in the package or at stored procedure so anybody can use the same package.
Thanks
October 23, 2013 at 7:43 am
You need to confirm that you do indeed have the same permissions on the database. Authentication is a bit weird, but if you both get the same results from this code, then you probably have the same permissions. If you don't, that's one place I would look next:
SELECT DISTINCT lt.name FROM sys.login_token lt INNER JOIN sys.server_principals sp ON sp.sid = lt.sid
It may be that your colleague is getting access to the database via another group he's a member of that doesn't have permission to the proc. SQL will just pick one SID that allows access to SQL, and it's not necessarily the right one in all circumstances.
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]
October 23, 2013 at 7:46 am
Perhaps you can specify EXECUTE AS dbo in the stored proc and make sure your colleagues have execute permissions on the sp.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 23, 2013 at 8:19 am
Thank you, I will try these options to see if its working. However I have question that why it causing problem on access denied. isn't SP access works as same as table access since we all user have same access to database where SP gets created. And what should be done to make these SP can give access to others because I will be creating more SP for same database and will be used by others at the same time. Also do I need to make any changes inside of SSIS?
What is the difference between EXECUATE as CALLER and EXECUATE AS DBO?
October 23, 2013 at 8:30 am
For the difference between EXECUTE AS options, please consult:
http://msdn.microsoft.com/en-us/library/ms181362.aspx
and as for stored procedure permissions, please consult books online or MSDN. Just creating a proc doesn't mean that users have permissions to it. It's too large a topic to get into here, I think... 🙂
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]
October 23, 2013 at 10:01 am
Permissions on tables (select, insert, update, delete) are separate from permissions on stored procs (execute).
Execute on a proc allows a user to do anything the proc does in the same database, independent of the user's permissions on any tables touched by the stored proc.
October 24, 2013 at 7:05 am
Thank you for your help. When I tried to put Execute as dbo instead Execute as Caller at the end of SP, it give me an error as "incorrect syntax near 'dbo'",
Please help me here
October 24, 2013 at 7:13 am
I'm not 100% sure of the syntax.
Try EXECUTE AS 'dbo'
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply