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

▲
7
▼

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
database-administration
permissions
asked on Server Fault Jul 13, 2009 by Triynko

2 Answers

▲
4
▼

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?

answered on Server Fault Jul 13, 2009 by Adam Brand
▲
3
▼

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.

answered on Server Fault Jul 22, 2009 by user13734 • edited Jul 27, 2009 by user13734

User contributions licensed under CC BY-SA 3.0