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.
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:
|
1 2 3 4 5 6 7 8 |
USE [CLR] GO ALTER DATABASE [CLR] SET TRUSTWORTHY ON GO EXEC dbo.sp_changedbowner 'usuario' GO |
Restoring the database SID automatically:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 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:
|
1 2 3 4 5 6 7 8 |
USE [CLR] GO ALTER DATABASE [CLR] SET TRUSTWORTHY ON GO ALTER AUTHORIZATION ON DATABASE::[CLR] TO [usuario] GO |
Restoring the database SID automatically:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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, 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!
