SQL SERVER – Encrypted Stored Procedure and Activity Monitor

I recently had received question if any stored procedure is encrypted can we see its definition in Activity Monitor.

No.

Let us do quick test. Let us create following Stored Procedure and then launch 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?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Happy Birthday to you!
About these ads

11 thoughts on “SQL SERVER – Encrypted Stored Procedure and Activity Monitor

  1. 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.

    Like

  2. 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.

    Like

  3. 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

    Like

  4. Per Books On Line (http://msdn.microsoft.com/en-us/library/ms187926.aspx),

    “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?

    Like

  5. 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

    Like

  6. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 20 of 31 Journey to SQLAuthority

  7. Pingback: SQL SERVER – Cases When Stored Procedure RECOMPILE – Quiz – Puzzle – 14 of 31 « SQL Server Journey with SQL Authority

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #050 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s