Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
gregoryagu
Yak Posting Veteran
80 Posts |
Posted - 2013-07-04 : 09:43:48
|
I have an app which calls a SP, which in turn calls a CLR Stored Procedure.The CLR stored procedure calls a number of different tables, using a Context connection string.The issue is that the CLR SP requires the user to have permissions to the tables directly, instead of just permissions to the SP which was expected.If I just give permission to the SP, then the CLR SP fails. So I then add the table permissions, and it then works.So the question is, how do I raise security so the app does not have permissions on the tables?Greg |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-04 : 10:47:03
|
In a non-CLR stored procedure, you are able to grant permission to the stored procedure and have it access the tables even though the caller does not have permissions on the table because of "Ownership Chaining". See a brief description here: http://msdn.microsoft.com/en-us/library/ms188676(v=sql.105).aspxHowever, there are certain situations which break the ownership chain, and CLR stored procedures happen to be one of those (along with dynamic SQL etc.) So you cannot use the tables without granting select permissions on the table if you rely on ownership chaining.Another possibility, and I have not investigated this, so I am just throwing this out there, might be the use of certificates. See the reply by TG in this thread to see if that helps: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=183685 |
|
|
gregoryagu
Yak Posting Veteran
80 Posts |
Posted - 2013-07-04 : 10:58:09
|
Thanks very much.Greg |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-04 : 11:04:36
|
You are welcome; If you do find a solution either using certificates or some other ways to do this (without granting select permissions on the underlying tables), I would like to learn how you did that. |
|
|
gregoryagu
Yak Posting Veteran
80 Posts |
Posted - 2013-07-08 : 09:26:54
|
James,Here is the solution that was found on this: "WITH EXECUTE AS SELF"This, as I understand it, causes the SP to execute with the permissions of the person who created the stored procedure, and thus does not need the extra permissions.CREATE PROCEDURE ... WITH EXECUTE AS SELFAS EXTERNAL NAME ... Greg |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-08 : 10:50:04
|
Thanks for posting back, Greg. Glad you got it figured out. |
|
|
|
|
|
|
|