Solved

ALTER AUTHORIZATION

Posted on 2010-11-12
7
1,363 Views
Last Modified: 2012-06-27
is the below
ALTER AUTHORIZATION ON DATABASE:: <Dbse name>TO “<USER Name>“

the same as giving db_owner role to the user?
0
Comment
Question by:anushahanna
  • 3
  • 2
  • 2
7 Comments
 
LVL 7

Accepted Solution

by:
BobintheNoc earned 200 total points
ID: 34125543
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
 
LVL 6

Author Comment

by:anushahanna
ID: 34125962
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
 
LVL 7

Assisted Solution

by:BobintheNoc
BobintheNoc earned 200 total points
ID: 34126124
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
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.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 300 total points
ID: 34129092
>>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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 300 total points
ID: 34129103
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 300 total points
ID: 34129114
>>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
 
LVL 6

Author Comment

by:anushahanna
ID: 34160856
Thanks to Bob & Thanks for the clarification, Anthony.. helpful.

0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question