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.

Read more: Journey to SQL Authority with Pinal Dave

Posted via email from .NET Info