CLR stored procedure is automatically encrypted

Question

I have created a simple CLR procedure that creates a Folder. The problem is that each time I create the procedure inside SQL Server using the "CREATE PROCEDURE..." statement, the procedure is automatically encrypted. I am not using the WITH ENCRYPTION clause and I am also aware that it cannot be used for CLR objects so I am confused here. Here is my code:

CREATE PROCEDURE CreateFolder(@Path nvarchar(500), @FolderName nvarchar(50), @ReturnCode int output, @ReturnMessage nvarchar(500) output )
WITH EXECUTE AS OWNER 
AS
EXTERNAL NAME MyLibrary.[MyLibrary.StoredProcedures].CreateFolder
GO

I am using SQL Server 2008 R2 workgroup edition.

This is the screenshot

Any help is much appreciated.

Thank you.

Jared Peless · Accepted Answer · 2012-01-01 02:41:35Z

2

Is it just showing it as encrypted because you can't see the code for it in Management Studio? You can't script to ALTER or anything so that is likely why.

answered Jan 1, 2012 at 2:41

Jared Peless

1,1209 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fayde Over a year ago

You were right. I can't open the procedure but I can still see the code by scripting it as "create". - Thank you.

Solomon Rutzky · Accepted Answer · 2015-09-01 23:34:15Z

SQLCLR objects are not encrypted. In fact, as noted in the Question, the WITH ENCRYPTION option isn't even available for SQLCLR objects. The reason that they appear as encrypted in SSMS is not due to not being able to see their definition, it is due to them not even having a definition in the first place. If you look in sys.sql_modules you can see definitions for Stored Procedures, Functions, Triggers, Views, etc. But none of them are SQLCLR objects. Also, if you look at the sys.parameters catalog view, there is a field called has_default_value and according to that linked MSDN page:

SQL Server only maintains default values for CLR objects in this catalog view; therefore, this column has a value of 0 for Transact-SQL objects. To view the default value of a parameter in a Transact-SQL object, query the definition column of the sys.sql_modules catalog view, or use the OBJECT_DEFINITION system function.

The CREATE statements for SQLCLR objects (like the one shown in the Question) are generated from meta-data each time that they are requested.

Collectives™ on Stack Overflow

CLR stored procedure is automatically encrypted

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related