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.’
Resolution:
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:-
https://stackoverflow.com/questions/12389656/the-database-owner-sid-recorded-in-the-master-database-differs-from-the-database
For me, below solution provided by Stefan Steiger worked perfectly.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
-- 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)) , N'<<LoginName>>' , QUOTENAME ( COALESCE ( SL.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() PRINT @command EXECUTE(@command) GO |
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.
|
1 2 |
EXEC master.dbo.sp_cycle_errorlog GO |
Friends, please feel free to correct me by comments. If you like the article, do Like & Share. Happy Coding 🙂
Related Posts
About The Author
Ajay Dwivedi
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.