SQL Server - The Database Owner SID Recorded in the Master Database Differs from the Database Owner SID Recorded in Database

Hey guys,
Good afternoon!

In this post I will demonstrate how to solve a common problem in the development of CLR libraries that occurs when you restore a CLR database from another server. Although the solution is not very simple, when you encounter this problem for the first time, it may take you a while to resolve it.

Identifying the problem and understanding why it happens

After restoring the CLR database on the destination server, when trying to execute any procedure or function, the following error message occurs:

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

This error can occur when the SID of the owner of this restored database is not present in the list of owners of the master database of your instance.

The solution is very simple and just reset the owner of the restored database (you can change it to another owner and return to the owner with the same name that exists in the instance). This can be done through the SQL Server Management Studio interface, using sp master.dbo.sp_chagedbowner or with the ALTER AUTHORIZATION command.

Changing the owner through the SQL Server Management Studio interface

To change the database owner, simply right-click on the database in the “Object Explorer” screen and select the “Properties” option. After that, select the “Files” menu and then you can change the bank owner and confirm.

Changing the owner with sp_changedbowner

One way to change the owner using T-SQL is using the sp_changedbowner procedure, available from version 2008 to 2014 and is marked as deprecated in future versions of SQL Server.

Example:

USE [CLR]
GO
 
ALTER DATABASE [CLR] SET TRUSTWORTHY ON
GO
 
EXEC dbo.sp_changedbowner 'usuario'
GO

Restoring the database SID automatically:

USE [CLR]
GO

DECLARE 
    @Comando VARCHAR(MAX)

SELECT
    @Comando = 'EXEC dbo.sp_changedbowner ' + QUOTENAME(B.name)
FROM
    sys.databases                       A
    JOIN sys.server_principals          B	ON	B.sid = A.owner_sid
WHERE
    A.name = DB_NAME()


PRINT @Comando
EXEC(@Comando)

Changing the owner with ALTER AUTHORIZATION

Another way to change the owner of a database in SQL Server via T-SQL is to use the ALTER AUTHORIZATION command on the target database, as shown in the example below.

The advantage of this solution for using sp_changedbowner is that the ALTER AUTHORIZATION command has been available since version 2008 (as well as SP), but it is not marked as deprecated, that is, it will be available in future versions of SQL Server.

Example:

USE [CLR]
GO
 
ALTER DATABASE [CLR] SET TRUSTWORTHY ON
GO
 
ALTER AUTHORIZATION ON DATABASE::[CLR] TO [usuario]
GO

Restoring the database SID automatically:

USE [CLR]
GO

DECLARE 
    @Comando VARCHAR(MAX) = 'ALTER AUTHORIZATION ON DATABASE::<<DatabaseName>> TO <<LoginName>>',
    @Usuario VARCHAR(MAX) = ( SELECT TOP 1 name FROM sys.server_principals WHERE type_desc = 'SQL_LOGIN' AND is_disabled = 'false' ORDER BY principal_id ASC)

SELECT 
    @Comando = REPLACE(REPLACE(@Comando, '<<DatabaseName>>', QUOTENAME(A.name)), '<<LoginName>>', QUOTENAME(COALESCE(B.name, @Usuario)))
FROM
    sys.databases                               A
    LEFT JOIN sys.server_principals             B	ON	B.sid = A.owner_sid
WHERE
    A.name = DB_NAME()

PRINT @Comando 
EXEC(@Comando) 

That's it, folks. After adopting one of the solutions listed above, your routines and functions in the restored CLR database are now working correctly.

If you have any questions, just leave them in the comments.
Hug!