Changing database owner in SQL Server 2008; CLR issues depending on method used?
I attached a database and tried changing the owner to a valid login.
I used the statement: ALTER AUTHORIZATION ON database::my_db_name TO "sa". The database properties showed that the new owner was 'sa', however I was still getting permission errors for unrestricted CLR assemblies (0x80FC80F1, 0x8013150A), something about assembly trust issues.
I resolved the issue by instead using the statement: EXEC sp_changedbowner 'sa'; to change the database owner.
My question is, what's the difference between these two methods of changing the database owner. Are they equivalent? It seems clear to me that sp_changedbowner is doing something more/correct that the alter authorization statement is not doing.
In case you're interested... before fixing things with sp_changedbowner, I tried:
- setting the database's trustworthy property to ON; in fact, I did this a few times; I know it's a requirement to run unrestricted, unsigned custom CLR assemblies
- changing each CLR assembly's owner to dbo, since the owner was blank, but apparently dbo was already the owner, and it's just always blank in SSMS.
- changing each CLR assembly's owner to something else, but that doesn't work, because assemblies with dependent assemblies seem to always need the same owner; but it's impossible to change the owner simultaneously on both with the interface provided.
- calling GRANT UNSAFE ASSEMBLY to [sa]; apparently you can't grant permissions to that built-in account, along with a few others; they already have permission
- calling GRANT UNSAFE ASSEMBLY to [NT AUTHORITY\NETWORK SERVICE] (the account calling methods in the assemblies); no errors, but didn't appear to achieve anything (maybe changed the error number? the message never changed though).
- ...and probably a few other things I can't remember.
On your list I don't see seting up the database as trustworthy, so I assume that you forgot this step:
ALTER DATABASE my_db_name SET TRUSTWORTHY ON;
But maybe not...
Checking with this article: http://support.microsoft.com/kb/918040 it seems that they indeed sugest using sp_changedbowner instead of ALTER AUTHORIZATION. But the fact is, it does exactly the same thing (sp_changedbowner calls ALTER AUTHORIZATION under the covers). The difference is that it also removes "aliases" for the dbo user (deprecated functionality anyway) plus forces a checkpoint of the database. That last piece may be the one you are looking for.
I believe ALTER_AUTHORIZATION and sp_changedbowner can both change ownership of the database object. The difference between the commands, of course, is that ALTER_AUTHORIZATION can change other things (like the ownership of tables), whereas sp_changedbowner is just for changing the owner of the database.
The behavior you indicated sounds very strange, though. Can you replicate it?