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.

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.

• Consulting 101 – Why Do I Never Take Control of Computers Remotely?
• Consulting 102 – Why Do I Give 100% Guarantee of My Services?
• Consulting 103 – Why Do I Assure SQL Server Performance Optimization in 4 Hours?
• Consulting 104 – Why Do I Give All of the Performance-Tuning Scripts to My Customers?
• Consulting 105 – Why Don’t I Want My Customers to Return Because of the Same Problem?
• Consulting Wrap Up – What Next and How to Get Started

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