views:

875

answers:

1

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.

+1  A: 

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.

Chris KL