Voice of the DBA

Tag: T-SQL

Decrypting Stored Procedures The Cumbersome Way
I had a client that was struggling with some encrypted stored procedures. They needed to decrypt them, which I know is a pain in the #@$%@#$@#$#@. I had to do this one. This post shows how I sent them some code to do this.

Note, SQL Compare 15 does this easier and simpler. If you own it, I’d use that instead. A future post will show how easy that it.

Setup

I tested this on SQL Server 2017/2019/2022. I don’t have older instances handy, so I can’t verify that for those. However, I ran this code in databases on each instance:
```
CREATE OR ALTER PROCEDURE dbo.DecryptionTest
WITH ENCRYPTION
AS
SELECT 2 AS Two;
DECLARE @i INT = 1;
IF @i = 2
   SELECT 3 AS Two;
ELSE
   SELECT 2 AS Two;
GO
CREATE PROCEDURE [dbo].[DecryptionTest2]
WITH ENCRYPTION
AS
   SELECT 2 AS Two;
GO
```
I had shown how to detect these are encrypted in a previous post.

Decryption

I had initially sent the client these links, since I was sure they’d worked at one point. I thought that SQL Server 2000 and earlier had a different algorithm. Don’t quote me on that and there’s so much Google-noise, I can’t verify this.
- https://www.sqlservercentral.com/blogs/simple-way-to-decrypt-sql-server-stored-procedure
- https://www.sqlservercentral.com/scripts/decrypt-stored-procedures-views-and-triggers
Those links don’t work, and I can only guess that either I had used different procs or these were edited.

In any case, I found a gist here: https://gist.github.com/jstangroome/4020443

This has code that does work. I ran this on my instances, and I got similar output to this on all instance:

I didn’t clean this or try to extract the value from the XML, but for the client this worked.

It’s not the cleanest or best way, but it does decrypt the procs I’ve created.
August 14, 2023
Finding Encrypted Stored Procedures–#SQLNewBlogger
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.
August 9, 2023
Finding Identity Columns–#SQLNewBlogger
I had to find a set of identity columns recently and through this would make a good blog post.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Getting a List of Tables and Columns with Identity Properties

Finding out which tables have an identity isn’t very easy, especially in SSMS’s Object Explorer. This property is somewhat hidden, which is annoying to me as I use them often. However, I get this is just a property.

This is, however, stored in sys.columns.is_identity. This is set to 1 if the property is set, so filtering on this is good thing. If we join to sys.columns on object_id, we can get a list of table names.

Here’s a short script to do this.
```
SELECT
   o.name AS TableName
, columns.name AS ColumnName
, is_identity
FROM
   sys.columns
   INNER JOIN sys.objects AS o
     ON o.object_id = columns.object_id
WHERE sys.columns.is_identity = 1;
```
However, there’s an easier way. There is a sys.identity_columns view which inherits from sys.columns and does the filtering for you. You can use this code instead.
```
SELECT
  o.name AS TableName
, columns.name AS ColumnName
, is_identity
FROM
  sys.identity_columns AS columns
  INNER JOIN sys.objects AS o
    ON o.object_id = columns.object_id
```
SQL New Blogger

I had to do this as a quick test for a client that wanted to do some checking of identity seeds. They asked for a list of tables to check, and I showed them how to get this quickly.

This was literally about 2 minutes to set up and about 5 minutes to write this post. Something you could easily do.
August 2, 2023
T-SQL Tuesday #164: Code that makes you feel
The invitation this month is from Erik Darling, and it’s a neat one. I like this thought, asking us to find code that impressed us or made us feel something. I tend to look at this as positive, but it could be negative.

In any case, I was on vacation from 1 Jul to 9 Jul, out of touch with the world and unwired. So I’m doing this as a quick post by the seat of my pants. I might have to come back and make a second post in the future.

Changing the way I think about T-SQL

I’m a decent T-SQL developer. Not amazing, or great even, but I am effective. I’ve learned a lot over the years and I’ve been able to get things done for my employers. I often look at other’s code and I try to improve how I view problem solving. I’ve learned a lot from Itzik and others over the years, though I have to admit that many of us solutions go over my head. I’m just not in those spaces where I need complex coded solutions very often.

That being said, years ago I got an article from Jeff Moden on the tally table. I hadn’t used this, and was fascinated. I know Itzik had written about numbers tables early on, but it hadn’t caught my attention. However, in a follow-up, Jeff wrote about a splitter function, which would use the tally table to split strings efficiently. This is the function (credit to Jeff in his article):
```
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;
```
Now, note this is not the original code, but updated and improved code. I love that Jeff has maintained this.

What I found great is how Jeff approached the problem. This is limited to 8k strings, but that’s for performance reasons. One could easily enhance this to be larger if needed. I also like the tally table is quickly generated from simple code that gives us 10,000 numbers.

I also found the simplicity of the substring and the charindex to be something I think I not only understand, but could have written.

Could have. Certainly didn’t and might not have. This is great code that’s been helpful to me over the years in places where I wanted to break up code. I’ve used this in a number of demos for clients and I’ve referred people to this over the years as well.
July 11, 2023