记录在主数据库中的数据库所有者 SID 与数据库所有者 SID 不同

小开

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)

小开

最佳答案

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)

小开

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

小开

Apply the below script on database you get the error:

EXEC sp_changedbowner 'sa'


ALTER DATABASE [database_name] SET TRUSTWORTHY ON

小开

The simplest way to change DB owner is:

EXEC SP_ChangeDBOwner 'sa'