Solved

ALTER AUTHORIZATION

Posted on 2010-11-12
7
1,349 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now