SQL SERVER – Find Owner of Database – Change Owner of Database

Recently I had the opportunity to help a client with Comprehensive Database Performance Health Check, and while working together we ended up in a very interesting situation where they had one database without an owner. As there was no owner, it was not possible to open that database in the SSMS. We had to find Owner of the Database and change it later on. Let us see how we can do that with the help of T-SQL Script.

SQL SERVER - Find Owner of Database - Change Owner of Database FindOwner-800x543

Script – Find Owner of Database

SELECT 
    name AS [Database Name], 
    suser_sname( owner_sid ) AS [Database Owner Name]
FROM 
    sys.databases

Now if you see any database without any owner, you can easily change the owner to your preferred owner. Here is the script for the same.

Script – Change Owner of Database

USE [YourDB]
GO
EXEC sp_changedbowner 'sa'
GO

Here I have selected the user as sa but in the real world, you should select the most appropriate user for this database.

Let me know if you have any questions about this blog post by leaving a comment or reaching out to me via Twitter.

Here are six-part blog post series I have written based on my last 10 years of experience helping with the Comprehensive Database Performance Health Check. I strongly recommend you to read them as they walk you through my business model.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

, ,
Previous Post
SQL SERVER – Sample Script for Compressed and Uncompressed Backup
Next Post
SQL SERVER – Reasons – Not Able to Shrink the Transaction Log

Related Posts

1 Comment. Leave new

  • Andrey Samykin
    May 3, 2022 8:09 pm

    This script works also:
    EXEC [YourDB].dbo.sp_changedbowner ‘sa’ /*obsolete*/
    or
    ALTER AUTHORIZATION ON DATABASE::[YourDB] TO sa;

    Reply

Leave a Reply