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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 CLR Stored Procedures Table Permissions

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).aspx

However, 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
Go to Top of Page

gregoryagu
Yak Posting Veteran

80 Posts

Posted - 2013-07-04 : 10:58:09
Thanks very much.

Greg
Go to Top of Page

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.
Go to Top of Page

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 SELF
AS EXTERNAL NAME ...

Greg
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -