Solved

ALTER AUTHORIZATION

Posted on 2010-11-12
7
1,398 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

627 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