The database owner SID recorded in the master database differs from the database owner SID

Question

When I try to install tSQLt onto an existing database i get the following error:

The database owner SID recorded in the master database differs from the database owner SID recorded in database ''. You should correct this situation by resetting the owner of database '' using the ALTER AUTHORIZATION statement.

score 153 · Accepted Answer · edited Apr 05 '16 at 14:11

153

This problem can arise when a database restored from a backup and the SID of the database owner does not match the owners SID listed in the master database. Here is a solution that uses the "ALTER AUTHORIZATION" statement recommended in the error message:

DECLARE @Command VARCHAR(MAX) = 'ALTER AUTHORIZATION ON DATABASE::[<<DatabaseName>>] TO 
[<<LoginName>>]' 

SELECT @Command = REPLACE(REPLACE(@Command 
            , '<<DatabaseName>>', SD.Name)
            , '<<LoginName>>', SL.Name)
FROM master..sysdatabases SD 
JOIN master..syslogins SL ON  SD.SID = SL.SID
WHERE  SD.Name = DB_NAME()

PRINT @Command
EXEC(@Command)

edited Apr 05 '16 at 14:11

usr

168,620
35
240
369

answered Nov 01 '12 at 00:32

JohnnyM

28,758
10
38
37

1

Thanks! That seems more appropriate. Do you think it's not worthwhile to use quotename() instead of putting the '[' in the string? Also maybe selecting into var DBName and var LoginName then putting those together into var Command instead of using REPLACE()? – JDPeckham Nov 14 '12 at 19:19
3

If you have spaces or special characters like '-' in your DB name this script will give you error. So just put [] brackets like this: 'ALTER AUTHORIZATION ON DATABASE::[<>] TO [<>]' – buhtla Jan 25 '13 at 10:39
12

When I run this I get the error "The proposed new database owner is already a user or aliased in the database" – MobileMon Nov 27 '13 at 15:19
For this script, the inner join to syslogins does not work for me probably because the SID mismatch _is_ the problem. – crokusek Feb 12 '15 at 01:53

score 33 · Answer 2 · answered Sep 12 '12 at 13:45

33

Added this to the top of the tSQLt.class.sql script

declare @user varchar(50)
SELECT  @user = quotename(SL.Name)
  FROM  master..sysdatabases SD inner join master..syslogins SL
    on  SD.SID = SL.SID
 Where  SD.Name = DB_NAME()
exec('exec sp_changedbowner ' + @user)

answered Sep 12 '12 at 13:45

JDPeckham

2,414
2
23
26

This worked like a charm with `tSQLt Version: 1.0.5873.27393` and appears to be a simpler solution. Using MS SQL Server 2019 Developer and SSMS 18. – k_rollo Oct 04 '20 at 03:03

score 23 · Answer 3 · edited May 31 '16 at 11:12

23

Apply the below script on database you get the error:

EXEC sp_changedbowner 'sa'

ALTER DATABASE [database_name] SET TRUSTWORTHY ON

edited May 31 '16 at 11:12

sschale

5,168
3
29
36

answered May 31 '16 at 09:28

NarendraMishra

231
2
2

1

The second statement cases the following security vulnerability: VA1102 - The Trustworthy bit should be disabled on all databases except MSDB – Shadi Alnamrouti Dec 10 '19 at 07:06

score 6 · Answer 4 · answered Jan 22 '15 at 11:58

Necromaning:
If you don't want to use the SQL-Server 2000 views (deprecated), use this:

-- 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

Also prevents bug on oddly named database or user, and also fixes bug if no user is associated (uses sa login).

Shadi Alnamrouti · Answer 5 · 2020-09-10T22:08:03.507

6

The simplest way to change DB owner is:

EXEC SP_ChangeDBOwner 'sa'

edited Sep 10 '20 at 22:08

answered Dec 10 '19 at 07:07

Shadi Alnamrouti

11,796
4
56
54

Yes we have established that. – JDPeckham Dec 11 '19 at 14:33

The database owner SID recorded in the master database differs from the database owner SID

5 Answers5

Linked