Error creating assembly in SQL: MSG 33009

Question

I am trying to load a dll in MSSQL using

USE dbname GO CREATE ASSEMBLY foo FROM 'C:\foo\foo.dll' WITH PERMISSION_SET = UNSAFE GO 

And I get the error message:

  Msg 33009, Level 16, State 2, Line 2
 The database owner SID recorded in the master database differs from the database owner 
 SID recorded in database 'dbname'.  You should correct this situation by resetting the 
 owner of database 'dbname' using the ALTER AUTHORIZATION statement.

MSDN really no longer tells me about the error the error is talking about.

I looked all over the Internet and came to the conclusion that only what ever did to avoid this was:

 use dbname go EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false 

But is the owner really changing the only way to avoid this error? Why should I do this, is there any other way? I want more information about this error before I log in and blindly change the owner.

Answer 1

I had exactly the same problem and the only solution for me was changing the owner and then changing it back.

The problem is that users are for each database as well as for each server. It so happened that a database-based user has a username that matches the user name on the server, but the SIDs do not match, so he thinks that it could be a different person.

Answer 2

Something you might want to check: if the user you are logged in with (and create the database as) is also displayed in the "model" database, then this user will be created under the users tab for the new database . This means that the Security tab has credentials for the AND instance for local users for the database. To solve the problem immediately, drop the user from the local database - you can set them as the owner (from the credentials of the instance):

drop user [MyUser]; exec sp_changedbowner [MyUser]

To solve this problem in the long run, cancel the user configuration from the "model" database (Security / Logins / [MyUser] Properties - User Mapping).