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)?
这是因为这些是CLR存储过程吗?它们是“常规”程序,但以某种方式加密/保护?有没有办法获得那些代码(T-SQL或IL)?
5 个解决方案
#1
The padlock means that the stored procedure has been encrypted using the WITH ENCRYPTION hint (see CREATE PROC in BOL for more information).
挂锁意味着已使用WITH ENCRYPTION提示加密存储过程(有关更多信息,请参阅BOL中的CREATE PROC)。
It doesn't mean that it's a CLR stored procedure.
这并不意味着它是CLR存储过程。
Here's a SQL Server Magazine article on how to decrypt objects which are encrypted using the WITH ENCRYPTION hint.
这是一篇关于如何解密使用WITH ENCRYPTION提示加密的对象的SQL Server Magazine文章。
There are also third party tools which do the same thing - native sproc encryption is not meant to be a strong level of encryption.
还有第三方工具也做同样的事情 - 本机sproc加密并不意味着强大的加密级别。
Edit: Here's another, but I haven't tested it on SQL Server 2005 or later.
编辑:这是另一个,但我没有在SQL Server 2005或更高版本上测试它。
#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.
除了加密外,它还意味着您没有VIEW DEFINITION权限,因此无法查看存储过程的代码。
#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.
挂锁只是意味着它们是加密的 - 与CLR无关。无论它们是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:
你正在看的SP是CLR型SP。我刚做了一个成功的POC。您只需要浏览此链接:
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.
这些是使用WITH ENCRYPTION选项创建的存储过程(有关更多信息,请参阅有关CREATE PROCEDURE的MSDN文档)。这意味着你无法看到存储过程的代码。
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)
加密存储过程的副作用是无法查看这些对象的执行计划(通过DMV缓存执行计划或通过分析捕获的执行计划)
#1
The padlock means that the stored procedure has been encrypted using the WITH ENCRYPTION hint (see CREATE PROC in BOL for more information).
挂锁意味着已使用WITH ENCRYPTION提示加密存储过程(有关更多信息,请参阅BOL中的CREATE PROC)。
It doesn't mean that it's a CLR stored procedure.
这并不意味着它是CLR存储过程。
Here's a SQL Server Magazine article on how to decrypt objects which are encrypted using the WITH ENCRYPTION hint.
这是一篇关于如何解密使用WITH ENCRYPTION提示加密的对象的SQL Server Magazine文章。
There are also third party tools which do the same thing - native sproc encryption is not meant to be a strong level of encryption.
还有第三方工具也做同样的事情 - 本机sproc加密并不意味着强大的加密级别。
Edit: Here's another, but I haven't tested it on SQL Server 2005 or later.
编辑:这是另一个,但我没有在SQL Server 2005或更高版本上测试它。
#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.
除了加密外,它还意味着您没有VIEW DEFINITION权限,因此无法查看存储过程的代码。
#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.
挂锁只是意味着它们是加密的 - 与CLR无关。无论它们是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:
你正在看的SP是CLR型SP。我刚做了一个成功的POC。您只需要浏览此链接:
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.
这些是使用WITH ENCRYPTION选项创建的存储过程(有关更多信息,请参阅有关CREATE PROCEDURE的MSDN文档)。这意味着你无法看到存储过程的代码。
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)
加密存储过程的副作用是无法查看这些对象的执行计划(通过DMV缓存执行计划或通过分析捕获的执行计划)