SQL CLR Rights: Call Stored Procedure in CLR Procedure

Question

I have written a (private) stored procedure in T-SQL that is not visible for the user. He has no execute permissions.

CREATE PROCEDURE [dbo].[GetEntries]
AS
BEGIN 
    SELECT * FROM sometable
END

Then I wrote a CLR stored procedure, for which the user has the execute permissions. This stored procedure calls my private procedure.

using (SqlConnection conn = new SqlConnection())
{
    conn.ConnectionString = "context connection=true";
    conn.Open();

    SqlCommand command = new SqlCommand("exec dbo.[GetEntries] ", conn);
...

If I call the public CLR stored procedure, an error occured that I have no rights to call the private stored procedure. I think this is because my connection string is "context connection=true" so the stored procedure is in the user context (user connection).

When I write a public (user visible) T-SQL stored procedure, which calls the private stored procedure, I can execute this stored procedure.

CREATE PROCEDURE [dbo].TSQLPublicSP
AS
BEGIN
    Exec  dbo.[GetEntries]
END

So my question is, how can I set the connection string in the SQL-CLR stored procedure like the connection it is from the T-SQL stored procedure. I don't want to set the databasename in the SQL-CLR connection string. It would be ok for me if I could get the database name form the SqlContext:

SqlConnection("server=LOCALHOST;integrated security=yes;database=" &
  SqlContext.???CurrentDatabase???)

Solomon Rutzky · Accepted Answer · 2017-03-29 12:32:11Z

Yes, the Context Connection is in the security context of the caller (or of the User specified in the EXECUTE AS clause of the CREATE PROCEDURE statement, but the default is EXECUTE AS CALLER).

The reason that the behavior is different between the SQLCLR stored procedure and the T-SQL stored procedure is:

The T-SQL stored procedure is making use of ownership chaining to imply permissions on related objects of the same owner as the code being executed.
The SQLCLR stored procedure is effectively submitting Dynamic SQL (as there is no way to pre-compile code within SQLCLR objects) which breaks ownership chaining.

If you want special permissions, then you need to use module signing as follows:

Create a Certificate or Asymmetric Key in this Database
Create a User from the Certificate or Asymmetric Key
Grant EXECUTE permission on the "hidden" stored procedure to the Cert-based or Key-based User
Sign the non-"hidden" stored procedure (doesn't matter if the visible one is T-SQL or SQLCLR) using ADD SIGNATURE.
If you ever make any changes to the Assembly that contains the method for this SQLCLR stored procedure, you will need to re-apply the ADD SIGNATURE

Collectives™ on Stack Overflow

SQL CLR Rights: Call Stored Procedure in CLR Procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related