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.