SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 CLR Stored Procedures Table Permissions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gregoryagu
Yak Posting Veteran

80 Posts

Posted - 07/04/2013 :  09:43:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 07/04/2013 :  10:47:03  Show Profile  Reply with Quote
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 - 07/04/2013 :  10:58:09  Show Profile  Reply with Quote
Thanks very much.

Greg
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 07/04/2013 :  11:04:36  Show Profile  Reply with Quote
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 - 07/08/2013 :  09:26:54  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 07/08/2013 :  10:50:04  Show Profile  Reply with Quote
Thanks for posting back, Greg. Glad you got it figured out.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000