Home / server / Questions / 40158
Accepted
Triynko
Asked: 2009-07-14 13:27:23 +0800 CST

Changing database owner in SQL Server 2008; CLR issues depending on method used?

  • 772

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.
sql-server security permissions database-administration

2 Answers

  1. 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?

    • 4
  2. Best Answer

    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.

    • 3