change dbo of database
-
When I setup a test sql server 2008 r2 database, I just took all the defaults. As I found out latter, my domain\username is the dbo. However I would like to change the dbo to another person in the company and/or possibly use a sql login just created for this purpose. Thus can you tell me who sql and/or systems stored procedure I would use to change the person assigned to this database as dbo.
-
ALTER AUTHORIZATION ON DATABASE::[database name] TO [login name];
CEWII
-
You can also do this by right-clicking on the database, selecting "properties", and then follow your nose.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
you can also use sp_changedbowner but remember to 'use database_name' first!
-
SQLDBA360 (5/14/2012)
you can also use sp_changedbowner but remember to 'use database_name' first!sp_changedbowner is deprecated
The recommendation is to use ALTER AUTHORIZATION in T-SQL code although the SSMS team does not seem to have reacted to the deprecation notice as they still use the proc under the covers 😛
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Jeff Moden (5/13/2012)
You can also do this by right-clicking on the database, selecting "properties", and then follow your nose.Really??? Jeff, I must be dense, because the initial "Owner" field is always grayed-out (not writable) for me, and I can never find where else to change it in properties? ...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
RBarryYoung (5/14/2012)
Jeff Moden (5/13/2012)
You can also do this by right-clicking on the database, selecting "properties", and then follow your nose.Really??? Jeff, I must be dense, because the initial "Owner" field is always grayed-out (not writable) for me, and I can never find where else to change it in properties? ...
Hmmm, that technique has always worked for me. What version of SSMS and instance? Are you a sysadmin on the instance?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (5/14/2012)
RBarryYoung (5/14/2012)
Jeff Moden (5/13/2012)
You can also do this by right-clicking on the database, selecting "properties", and then follow your nose.Really??? Jeff, I must be dense, because the initial "Owner" field is always grayed-out (not writable) for me, and I can never find where else to change it in properties? ...
Hmmm, that technique has always worked for me. What version of SSMS and instance? Are you a sysadmin on the instance?
Well, I just checked 2012 and 2008r2, developer edition for both. And I am sysadmin on all of them.
Ahh, I see. It's under files? Silly me, I never looked there, I always looked under General, and Permissions. Not sure why it's under Files ... ???
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply