Error/Fix: The database owner SID recorded in the master database differs from the database owner SID recorded in database
Recently I received an incident for low disk space on mount point dedicated to system databases on development sql server. So I started checking SQL Server Error logs to find out what’s filling up the msdb database, and encountered multiple occurrences of error message similar to below:-
The activated proc ‘[dbo].[SqlQueryNotificationStoredProcedure-ac5720fb-f6f2-4df7-b6ca-6845b4c71d1a]’ running on queue DEV_SERVER_01.dbo.SqlQueryNotificationService-ac5720fb-f6f2-4df7-b6ca-6845b4c71d1a’ output the following: ‘The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘MY_DEV_DB01’. You should correct this situation by resetting the owner of database ‘MY_DEV_DB01′ using the ALTER AUTHORIZATION statement.’
As suggested by error message, this problem is logged when SID of the database owner does not match with SID of owner registered in master database. This could occur after DBA forgets to fix database owner after database refresh. So, I googled further, and found below URL that had multiple solutions by SQL Server experts:-
For me, below solution provided by Stefan Steiger worked perfectly.
-- Restore sid when db restored from backup...
DECLARE @Command NVARCHAR(MAX)
SET @Command = N'ALTER AUTHORIZATION ON DATABASE::<<DatabaseName>> TO <<LoginName>>'
SELECT @Command = REPLACE
REPLACE(@Command, N'<<DatabaseName>>', QUOTENAME(SD.Name))
,(SELECT TOP 1 name FROM sys.server_principals WHERE type_desc = 'SQL_LOGIN' AND is_disabled = 'false' ORDER BY principal_id ASC )
FROM sys.databases AS SD
LEFT JOIN sys.server_principals AS SL
ON SL.SID = SD.owner_sid
WHERE SD.Name = DB_NAME()
After resolving the SID mismatch issue, I went forward to recycle the SQL Server Error log files with below TSQL code till enough space could be freed on mount point.
Friends, please feel free to correct me by comments. If you like the article, do Like & Share. Happy Coding 🙂
Error/Fix: Msg 924. Get Database Out of Single User Mode
No Comments | Sep 23, 2022
Error/Fix: Database cannot be upgraded because it is read-only or has read-only files
No Comments | Jul 26, 2020
Performance Tuning With Predicate Logic
No Comments | Oct 15, 2016
Error/Fix: Cannot initialize the data source object of OLE DB provider “OraOLEDB.Oracle” for linked server. (.Net SqlClient Data Provider)
No Comments | Feb 20, 2020
About The Author
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.