I recently had the opportunity to assist a client with a Comprehensive Database Performance Health Check, and while working together, we came across an interesting situation in which they had one database without an owner. Because there was no owner, that database could not be accessed in SSMS. We had to track out the owner of the database and update it after that. Let’s look at how we can handle it with T-SQL Script.
- Script – Identify Database Owner
SELECT
name AS [Database Name],
suser_sname( owner_sid ) AS [Database Owner Name]
FROM
sys.databases
If you notice a database that does not have an owner, you may quickly change the owner to your selected owner. Here is the script for it.
- Script – Change Database Owner
USE [YourDB] GO EXEC sp_changedbowner 'sa' GO
In this case, I choose the user “sa,” but in actual situations, you should choose the best user for this database.
