Solved

ALTER AUTHORIZATION

Posted on 2010-11-12
7
1,375 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: 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!

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

756 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