Voice of the DBA

Tag: SQLNewBlogger

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
Which Schema is Queried First?–#SQLNewBlogger
I had to test something for a customer, and as a part of this there as a need to have a different default schema for a user. I wrote about that, but one of the things that occurred to me is the need to know how security works with schemas and the priority of queries.

The Scenario

A user in a database needed to access certain objects, which were going to be located in a separate schema. The previous post looked at the setup of the user, but what happens with queries. Let’s set up a few tables that will help us learn.

I’ll create two tables, one in each schema.
```
CREATE TABLE dbo.Location (locationname VARCHAR(20))
GO
INSERT dbo.Location (locationname) VALUES ('dbo schema')
GO
CREATE TABLE webapi.Location (locationname VARCHAR(20))
GO
INSERT webapi.Location (locationname) VALUES ('webapi schema')
GO
```
Let’s see what happens when we query the tables. If I write a query that qualified objects, I get what expected. I’ll login as APIUser and then run this code.
```
SELECT * FROM dbo.location
SELECT * FROM WebAPI.location
```
When I do this, I get the results expected.

If I don’t qualify the table, do you know what happens? You should, and this post shows the results that the default schema is queried first.

If I didn’t have a WebAPI.Location table, the dbo.location result would be shown. It is important that you understand SQL Server security if you manage instances or write queries, as you could otherwise feel you need to grant far too many permissions.

SQL New Blogger

This was a minor part of something else I was doing. I noted this in the previous post, but also realized the ALTER was a good second post and verifying the permission hierarchy was a good third post. I could have added this to the first one, but I like separating and focusing posts. Better for SEO if you care, but better for your workload and producing most posts.

Outside of the work I was doing, the sketch of these notes took about 2 minutes, and then the entire post was a little longer, with more code to write. This was about a 15 minute post.

You can do this.
July 26, 2023
Altering the Default Schema for a User
I had to test something for a customer, and as a part of this there as a need to have a different default schema for a user. I wrote about that, but since this isn’t something that I (or many people) do often, I wanted to make a second post about changing the schema.

The Scenario

A user in a database needed to access certain objects, which were going to be located in a separate schema. The previous post looked at the issue for a new user, but for this one, I wanted to show how to change a schema for an existing user.

This post shows how to alter a user’s default schema.

The Solution

When you add a user, this is a simple parameter as part of the CREATE USER DDL. In this case, you use the DEFAULT_SCHEMA parameter. The ALTER is the same, which isn’t the case with all parts of the T-SQL language. Sometimes there are procedures instead of true DDL.

In my case, we wanted to change the default schema for a user. In the first post, the APIUser had the default of the WebAPI schema. Let’s move them to the Sales schema with this code:
```
ALTER USER APIUser WITH DEFAULT_SCHEMA=Sales

GO
```
That’s it and now if objects aren’t schema qualified, the APIUser will query the Sales schema first, then the dbo schema. If this user wants to query the WebAPI schema, they must schema qualify things.

SQL New Blogger

This was a minor part of something else I was doing. I noted this in the previous post, but then realized the ALTER was a good second post. I could have added this to the first one, but I like separating and focusing posts. Better for SEO if you care, but better for your workload and producing most posts.

Outside of the work I was doing, the sketch of these notes took about 2 minutes, and then the entire post was < 10 minutes.

You can do this.
July 19, 2023