The database owner SID recorded in the master data

问题:

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.

回答1:

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)

回答2:

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)

回答3:

Apply the below script on database you get the error:

EXEC sp_changedbowner 'sa'

ALTER DATABASE [database_name] SET TRUSTWORTHY ON 

回答4:

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

回答5:

I ran into this issue as well, and found that the Owner of the target database didn't exist in the master database. Mapping that user to the master database resolved the issue for me.

回答6:

When want to import a new clr if you got this error message just set Sa as owner for your databse like below so should be work

Alter Authorization on Database::[] to [sa]