SQL SERVER – Encrypted Stored Procedure and Activity Monitor

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.

SQL SERVER - Encrypted Stored Procedure and Activity Monitor encryptedSP

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)

Happy Birthday to you!
, ,
Previous Post
SQLAuthority News – SQL Server 2008 Add-ins and Feature Pack Downloads
Next Post
SQLAuthority News – Presenting at South East Asia SharePoint Conference – Maintaining SQL Server at Optimal Performance for Blazing Fast SharePoint Site

Related Posts

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.

    Reply
  • Sumit Kumar Gaud
    October 11, 2010 12:22 pm

    This is really good. But now how to get your procedure text back to Alter that procedure.

    Reply
  • 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!

    Reply
  • 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.

    Reply
  • 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

    Reply
  • Nakul Vachhrajani
    November 26, 2010 2:56 pm

    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?

    Reply
  • 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

    Reply

Leave a Reply

Menu