Menu
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)?
362
To obtain information about locks in the SQL Server Database Engine, use the sys. dm_tran_locks dynamic management view.
SQL Server's built-in way to decrypt stored-procedures requires setting up a Dedicated Administrator Connection (DAC) to the SQL Server instance from SQL Server Management Studio on the server. You then need to extract the encrypted value of the procedure definition from sys.
Locks are held on SQL Server resources, such as rows read or modified during a transaction, to prevent concurrent use of resources by different transactions. For example, if an exclusive (X) lock is held on a row within a table by a transaction, no other transaction can modify that row until the lock is released.
As well as encrypted, it also means you don't have VIEW DEFINITION rights, so can't see the code of the stored procedure.
111
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.
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
