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)
8 Comments. Leave new
Well, pretty much all performance data for the encrypted procedure is available through DMVs and Profiler, however it is available only on a procedure level, and not on a statement level. You will be able to see the performance of the procedure as a whole and not for each statement.
This is really good. But now how to get your procedure text back to Alter that procedure.
Hi Dave,
what is real purpouse and limitations of encrypted stored procedures? Can you give me some real world example of its use?
Great articles btw!
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.
In that case i would use this below query to find the object name.
Select text,OBJECT_NAME(objectid) as objectname,* FROM sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle)
where encrypted=1
Per Books On Line ,
“However, the text will be available to privileged users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime. For more information about accessing system metadata, see Metadata Visibility Configuration.”
Wouldn’t we call viewing the execution plan or the Activity Monitor a debugging exercise?
How Can i Use Stored Procedure in C#.Net Window Application..
I am creating stored Procedure in sql server 2005..
And using Visual Studio 2008.
Thanks In Advance
See examples at http://www.asp.net