Error when creating assembly in SQL: MSG 33009

Question

I'm attempting to load a dll into MSSQL with:

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

And I'm getting an error that states:

 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 isn't telling me any more about the error than the error tells itself.

I've looked all over the internet and have come to the conclusion that only thing anyone has ever done to avoid this is to:

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

But is changing the owner really the only way to avoid this error? Why do I have to do this, is there another way? I'd like some more information about this error before I go in and blindly change the owner.

is 'dbname' A database that you restored from another server? If yes, then you have to re-assign ownership to the local SA. — StingyJack
– StingyJack, Commented Jan 30, 2009 at 21:57

Chris KL · Accepted Answer · 2009-02-05 03:52:43Z

I have had exactly the same problem and only solution for me was to change the owner, then change it back again.

The problem is that users are both per-database and per-server. What's happened is that the per-database user has a username that is the same as a per-server user, however they SIDs don't match, so it thinks it could be a different person.

Mike Hill · Accepted Answer · 2010-12-09 15:00:48Z

Something you might want to check: If the user that you are logged in as (and are creating the database as) is also mapped to the "model" database, then that user will be created under the users tab for the new database. This means that there are credentials under the Security tab for the instance AND for the local users for the database. To immediately solve the issue, drop the user from the local database - you can then set them back as the owner (from the instance credentials):

drop user [MyUser]; exec sp_changedbowner [MyUser]

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

Collectives™ on Stack Overflow

Error when creating assembly in SQL: MSG 33009

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related