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.

2 Answers 2

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.

1
  • Setting it trustworthy was actually the first bullet point on the list of things I tried (look again)... but anyway, that link you posted describes the issue well and seems to identify the cause. It must have something to do with the logins not being set up correctly; I thought I had linked them all to accounts on the new server, but perhaps in the mix of things to do it just took a while before all the necessary steps were completed.
    – Triynko
    Jul 24, 2009 at 14:29
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?

1
  • I will attempt to replicate the problem, but not until later today when I'm sure no tests are running against it.
    – Triynko
    Jul 14, 2009 at 14:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .