I had a client ask about how to deal with encrypted stored procedures in their database. This post looks at how to find them and I’ll have future posts that show how to decrypt these and also how Flyway helps.
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
The Scenario
A client was trying to start putting their code in a VCS, but they ran into the issue that they had procedures which were encrypted. In their case someone had done this in the past and the current staff wanted to know how to get the code from production.
As a setup, here are two procs I’ve created that are encrypted:
CREATE PROCEDURE EncryptedOne WITH ENCRYPTION
AS
SELECT 1 AS One
GO
CREATE PROCEDURE EncryptedTwo WITH ENCRYPTION
AS
BEGIN
DECLARE @i INT = 1;
WHILE @i < 100
BEGIN
SELECT @i = @i + 1
END
SELECT @i / 50
END
These two procs don’t do anything weird, but if I try to script them like this in SSMS:
I get an error:
The error says that the text for the procedure is NULL.
The text is stored in the sys.syscomments DMV, which we can see below. This is the text that was submitted for procedures without the WITH ENCRYPTION. You can also see a NULL entry for the procedures I created above.
I can filter on this with a
WHERE [text] IS NULL
Or I could use ObjectProperty(). This has an IsEncryted parameter I can send in with this code:
select name, OBJECTPROPERTY(object_id, 'IsEncrypted') AS Encrypted, OBJECT_DEFINITION(object_id) AS Code
from sys.procedures
WHERE OBJECTPROPERTY(object_id, 'IsEncrypted') = 1
Of course the text column isn’t needed as all the code is null here. If I wanted just a list, I’d likely only have the first two columns.
That’s it. With this script I can see those procs which are encrypted. In my case, it’s four.
SQL New Blogger
This is a quick post that shows how to find those procedures (or views) which were created with WITH ENCRPTION. I’ve run into this a few times and while this is a focused, small post, I also took the opportunity to break this into multiple posts rather than doing just one long one.
You could do this as well and showcase how you break a problem down. This took me about 10 minutes to do this post.