Voice of the DBA

Tag: SQLNewBlogger

Changing the Owner of a Database #SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I had an authorization issue with my account, and I decided to delete it and re-add it. That’s the subject for another day, but before I could delete it, I had to remove the ownership of some databases. You can’t delete a login that owns databases.

I realized I wasn’t sure how to do this, so I wrote this post.

A Deprecated Proc

There used to be a dbo.sp_changedbowner proc that was used, but I know this is deprecated and it shouldn’t be used. It likely would work fine in SQL Server 2019, but I also know there should be more modern code. I decided to look, as I ought to know what is recommended these days.

In searching around MS Docs, ALTER AUTHORIZATION comes up in the list. I checked, and this allows me to transfer the ownership of a securable, which a database is one of the items in the list. Example F shows what I want to do and uses this code:
```
ALTER AUTHORIZATION ON DATABASE::dbname TO [login]
```
I can replace dbname and login with the values I need.

Which Databases?

I have a lot of databases, and I don’t need to change them all, though I could. In my case, I decided to get a list of databases and owners. If you query sys.databases, there is an owner_sid column. If you join that with sys.server_principals, you can do so on the SID column. This query shows me what I need:
```
SELECT d.[name], sp.[name] FROM sys.databases d
  INNER JOIN sys.server_principals AS sp
   ON d.owner_sid = sp.sid
```
The results are here:

In some sense I hate that “sa” isn’t the default owner, but I get it. There might be a need for other accounts. However, my account is a sysadmin, so my view here is that “sa” ought to be listed.

I digress. Now that I have a list, I can limit it to my account with a WHERE clause. I can take that list of items and build the code. I could use a cursor, but this is a one-off task, so this works:
```
SELECT
                'ALTER AUTHORIZATION ON database::' + d.[name] + ' TO sa;'
              , d.[name]
              , sp.[name]

FROM
                sys.databases d
     INNER JOIN sys.server_principals AS sp
         ON d.owner_sid = sp.sid

WHERE          sp.name = 'ARISTOTLE\Steve';

GO
```
This gives me the code in the results I want to run. I copy paste this and I have a bunch of statements to run.

Despite Grammarly not being happy, this worked fine.

SQL New Blogger

As soon as I realized I needed to do this, I knew there were two posts here. One on the removal and adding back of my Windows account, and the second on this topic (when the first didn’t work).

This took about 15 minutes extra, finding the docs and writing some code, but it’s a good example of where a small situation that occurred helped me find something to write about. Easy for you to take little tasks like this and document your knowledge when you learn something.
March 14, 2022
Getting a Day Difference in PowerShell–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

SSMS froze on me the other day. Actually, it lightly responded to some things, but the window wouldn’t redraw and I couldn’t see the query window. I could see the results pane, but couldn’t get the app to respond.

I wanted to get the difference between two dates, and wasn’t sure, so I quickly searched. I tried assigning the date to a variable, but this creates a string:
```
$start = “2020-03-11”
```
With a couple searches, I learned I can use Get-Date to get a date variable. In this case, I’d do this:
```
$start = Get-Date -Date "2020-03-11"
```
If I did that with two dates, I could get the difference. Here’s a screen show that shows I get the result in a variety of different time slices.

If I wanted just days, I could do this:
```
($end - $start).Days
```
That returns just the 712.

I also learned I could shortcut this with a TimeSpan type.

New-TimeSpan -Start “2020-03-11” -End (Get-Date)

I get the same spread of time parts as the image above, or I can enclose this all in parenthesis and then call the “Days” property to get that value.

SQL NewBlogger

I hadn’t done much with date and time in PoSh, and after seeing an article from an author, I investigated a bit more. This was a part of what I tried to do, albeit as a response to something not working as expected.

Good to know how to work with dates, as I can see this being a part of many PoSh scripts that might clean up old files or otherwise take action based on time values.

You could write this post in about 10-15 minutes and show how you use PoSh to work with date and times.
February 21, 2022
Checking if a database has a master key–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I’ve been working with encryption in SQL Server for a long time, and have delivered quite a few presentations on the topic. Recently I was updating some code and wanted to check if a database had a master key created in it. This post shows how to do that.

The DMK (Database Master Key) is a construct that lives inside a database and provides the basis for encrypting other keys. It is a symmetric key, but created with the CREATE MASTER KEY DDL.

Information about this key is stored in a couple of places. First, it appears in sys.symmetric_keys, with the name “##MS_DatabaseMasterKey##”. You hsould see this with the AES_256 algorithm.

You can also query the sys.databases DMV for the is_master_key_encrypted_by_server c0lumn, if you keep the defaults. If you run this
```
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
```
then the sys.databases DMV will show 0, even though you still have a master key, as shown below.

SQLNewBlogger

A quick post. I was updating code to make it cleaner and realized I needed to add a check for the key. In the past, I’ve just ignored the error, but I took the chance here to refactor things and also produce a quick post.
January 26, 2022
Creating a new User-Defined Data Type–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I ran across a question on user-defined data types, which I hadn’t worked with in a long time, so I took a minute to investigate. I wrote this post about an interesting language item.

User-defined table types let you add a new type that can be used anywhere you would use a base, or normal, data type. This means if I want to have a type of US zip codes, perhaps limiting the base zip to 5 numbers, I can create a type that is limited to 5 digits.

If I want to create a new user-defined table type, I’d have thought I did something like this:
```
CREATE TYPE dbo.USZipCode AS VARCHAR(5)
```
However that doesn’t work.

The AS structure is used in many places, but not here. Instead, we use a FROM structure. This means I’d do this:
```
CREATE TYPE dbo.USZipCode FROM VARCHAR(5)
```
This gives me a type I can use in CREATE TABLE statements, stored procedures, and more. Anywhere I’d use the varchar(5), I could do this instead:
```
CREATE TABLE dbo.AddressTable

( AddressID INT NOT NULL

, AddressValue VARCHAR(100)

, AddressZip USZipCode

)
```
This let’s me use a type that is more intuitive, I guess. I don’t find these that useful in most places. In fact, it’s a little confusing. If I were a new developer, is this a 5 or 9 (or 10) digit field? Is it numeric or string? It’s not easy to determine this. I don’t find these that useful.

SQL New Blogger

I was doing other work, but I saved a bit of code and then spent about 10-15 minutes to write up this post. This one shows less about what I learned, and more about what I think.

Always good to show to a prospective interviewer.
January 19, 2022