Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1425
  • Last Modified:

ALTER AUTHORIZATION

is the below
ALTER AUTHORIZATION ON DATABASE:: <Dbse name>TO “<USER Name>“

the same as giving db_owner role to the user?
0
anushahanna
Asked:
anushahanna
  • 3
  • 2
  • 2
5 Solutions
 
BobintheNocCommented:
I don't believe so.  Today, one of our SQL database servers was throwing red flags in the App Event log, indicating a discrepancy between the master DB and another DB having different/mismatched SIDs.  We had to, under advice of a KB article, perform a ALTER AUTHORIZATION on the master, resetting it back to SA and then back to get it to stop.  Kinda funny coincidence that you posted a question on something that I had to work on today.

If it were JUST setting DBO, I'd have figured we would have been able to do it within the security/roles instead of at the DB level.
0
 
anushahannaAuthor Commented:
Bob, in other words, are you saying "ALTER AUTHORIZATION ON DATABASE" makes the user a dbo of the database (which is different than the db_owner)- a DB can have one dbo and multiple db_owners, if i am right...
0
 
BobintheNocCommented:
I'm just chiming in, but NO, I don't believe the dbo is the same thing as the owner.  DBO is definitely database owner, at the sql application level.  The ALTER functionality modifies though, from what I'm gathering, appears to be below application, similar, because I don't know any better, to be more like OBJECT based, similar to NTFS based ownership.

The only reason I suspect this is that from my experience today, if it were just the equiv of DBO, I'd have had a mechanism within Sql Mgmt Studio to modify via GUI instead of the MS described the alter command.  If it were just a right click kind of thing, I'd have figured it was just adding the role of DBO vs. actually using alter authorization.

Not sure if this makes sense, forgive me, it's a Friday night and Friday's are Beer Fridays on Cal-Train (my commute to San Francisco is via CalTrain)

I had a scenario today that required me to use ALTER Authorization instead of simply assigning DBO roles--which leads me to believe that DBO is 'different' from the authorization.   I'm probably off base, but I'm expecting that the KB article I had to reference made use of the ALTER Authorization instead of, IMHO, a simple ROLE of DBO.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Anthony PerkinsCommented:
>>the same as giving db_owner role to the user? <<
In a nutshell ALTER AUTHORIZATION DATABASE is the same as the old sp_changeownerdb.  That means that <USER Name> will become the owner of the database.
0
 
Anthony PerkinsCommented:
And this:
In a nutshell ALTER AUTHORIZATION DATABASE is the same ...
Should have read:
In a nutshell ALTER AUTHORIZATION ON DATABASE is the same ...
0
 
Anthony PerkinsCommented:
>>We had to, under advice of a KB article, perform a ALTER AUTHORIZATION on the master, resetting it back to SA and then back to get it to stop. <<
From SQL Server BOL:
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/8c805ae2-91ed-4133-96f6-9835c908f373.htm
Cannot change the owner of system databases master, model, tempdb, the resource database, or a database that is used as a distribution database. The principal must be a login. If the principal is a Windows login without a corresponding SQL Server login, the principal must have CONTROL SERVER permission and TAKE OWNERSHIP permission on the database. If the principal is a SQL Server login, the principal cannot be mapped to a certificate or asymmetric key. Dependent aliases will be mapped to the new database owner. The DBO SID will be updated in both the current database and in sys.databases.

In other words, if you do this:
ALTER AUTHORIZATION ON DATABASE::Master TO Somebody

You will get the following error:
Cannot change the owner of the master, model, tempdb or distribution database.
0
 
anushahannaAuthor Commented:
Thanks to Bob & Thanks for the clarification, Anthony.. helpful.

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now