SQL Server - The database owner SID recorded in the master database differs from the database owner SID recorded in database

Views: 583
Reading Time: 2 minutes

Hello people,
Good afternoon!

In this post I will demonstrate how to solve a common problem developing CLR libraries that occurs when you restore a CLR database from another server. Despite the quite simple solution, when you first encounter this problem, it may take a while to resolve.

Identifying the problem and understanding why it happens

After you restore the CLR database to the destination server, when attempting to perform 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 owner SID of this restored database is not present in the owner list of the database master of your instance.

The solution is very simple and just reset the owner of the restored database (can change to another owner and return to owner of 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, just 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's owner and confirm.

SQL Server - Change DB Owner

Changing the owner with sp_changedbowner

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

Example:

Restoring the database SID automatically:

Changing owner with ALTER AUTHORIZATION

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

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

Example:

Restoring the database SID automatically:

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

Any questions, just leave in the comments.
Hug!