What is a stored procedure with a padlock icon in

问题:

I see some stored procedures in one database I'm managing that have the regular stored procedure icon, but with a little padlock next to them.
The differences I see is that I can't "modify" them, and if I try to script them, it says:

Text is Encrypted.

Is this because these are CLR stored procedures?
Are they "regular" procedures, but encrypted/protected somehow?
Is there any way to get to the code of those (either the T-SQL or the IL)?

回答1:

The padlock means that the stored procedure has been encrypted using the WITH ENCRYPTION hint (see CREATE PROC in BOL for more information).

It doesn't mean that it's a CLR stored procedure.

Here's a SQL Server Magazine article on how to decrypt objects which are encrypted using the WITH ENCRYPTION hint.

There are also third party tools which do the same thing - native sproc encryption is not meant to be a strong level of encryption.

Edit: Here's another, but I haven't tested it on SQL Server 2005 or later.

回答2:

As well as encrypted, it also means you don't have VIEW DEFINITION rights, so can't see the code of the stored procedure.

回答3:

The padlock simply means they're encrypted - has nothing to do with them being CLR. There is no way to view the source regardless of them being CLR / T-SQL.

回答4:

The SP one your are looking at is CLR type SP. I have just done a POC which was successful. You need to only go through this link:

http://www.codeproject.com/Articles/37298/CLR-Stored-Procedure-and-Creating-It-Step-by-Step

回答5:

These are stored procedures created with the WITH ENCRYPTION option (see the MSDN Documentation on CREATE PROCEDURE for more information). All it means is that you cant see the code for the stored procedure.

It is possible to decrypt such stored procedures, but being that the idea behind encryption is that you cant do this its not straightforward - definitely dont do it on production servers! If you really need to see the stored procedure text then you are better off asking the people who wrote it for the unencrypted version first (you can at least try).

A side effect of encrypted stored procedures is that its not possible to view execution plans for those objects (either cached execution plans via DMVs or execution plans captured through profiling)