Changing the Owner of a Database #SQLNewBlogger | Voice of the DBA

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.