I recently had received a question if any, stored procedure is encrypted can we see its definition in Activity Monitor.
The answer is No.
Let us do a quick test. Let us create following Stored Procedure and then launch the Activity Monitor and check the text.
USE AdventureWorks GO -- Create Encyrpted SP CREATE PROCEDURE uspEnc WITH ENCRYPTION AS SELECT * FROM Sales.SalesOrderDetail UNION SELECT * FROM Sales.SalesOrderDetail GO -- Execute SP EXEC uspEnc GO
You will be not able to see the text of the encrypted Stored Procedure of the SQL Server.
What do you think? Is there any other way we can approach this?
UPDATE: Here is a very interesting answer from Dodo in the comment section.
I would decrypt the procedure. You can do it yourself or buy one of those many tools (SQL Decryptor, Decrypt SQL, etc, etc) that will do it for you. It will cost you (or your company) around. 150 dollar.
Looking at DBA point of view, I think encrypted stored procedures are horrible. You can’t see what queries are executed, you don’t have a clue what the execution plan is… So if users are complaining about the slowness of the system, and everything is encrypted… then it will be a hell of a job to tune the query….
If there’s a way that you can see the query if it’s executed from a encrypted stored procedure (besides decrypting that procedure) than I would love to hear about it!
About the purpose: I don’t have a clue! The only thing I can think of is that If you sell software that’s using a SQL Server database with stored procedures, customer’s with very little knowledge of T-SQL (and who do not have a DBA employed) can’t modify the procedures.
Reference: Pinal Dave (https://blog.sqlauthority.com)