Owner of database

Question

Owner of database

TRACEY-320982

SSChampion

Points: 13653
More actions
January 14, 2009 at 8:31 am

#133627

On a database you can right click the database and set the owner to be
Myuser.
Or you can select the user in the database and right click and set role member as db_owner
What is the difference between the two ways.
Thanks

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Mike - CI SSCarpal Tunnel Points: 4146 More actions · Answer 1

There can only be one owner (creator of the database), and I actually was not aware that you could change it through SSMS (though I assume there is a backend hack to it somewhere).

You can give as many people as you would like the role of db_owner.

GSquared SSC Guru Points: 260824 More actions · Answer 2

The difference is what is displayed when you look at the database properties. As far as who has what access to the database, there's no difference.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 3

I am aware of one owner and many users can be db_owner.

I wasn't sure if there are specific differences or why even do database owner and not just use

user...and set db_owner

Just curious...

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 4

The user who creates the database is set to as the owner of the database, which you can see in the options tab of properties...and yes, it can be changed from here. Usually this needs to be 'sa' or in some specific applications it can be a particular user or dbo.

Where any user can be adder to a role as db_owner for a database..

you can drop a user by taking it off the db_owner role, but you cannot drop a user if it owns the databse...

Pretty much its the same when it comes to deleting but with regards to creation of a database and assigning the owner it differs as I explained above..

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Hunting SSIS SSC Eights! Points: 943 More actions · Answer 5

FYI.

We cannot change the database owner by rightclicking the database and selecting properties.

We can change it by this command.

exec sp_changedbowner 'sa'

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 6

Actually that makes sense you could have the owner be one user (sometimes app need this set).

Example Owner APPUSER owner

Then you could have dev/programmers who need db_owner to create add/delete procs but they couldn't infact delete the DB itself because it is owned by APPUSER.

Now that makes sense to me....

Good explaination....

celestia SSC Veteran Points: 233 More actions · Answer 7

Hi - could you tell me how to change the DB owner by right-ckicking the DB in SSMS please? I've looked at the Options (SQL Sever Standard) tab as The_SQL_DBA suggests, but I can't see it - probably need coffee ... Thank you.

Hunting SSIS SSC Eights! Points: 943 More actions · Answer 8

you cant change it by rightclicking anything....

use have to use this SP

exec sp_changedbowner 'sa'

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 9

In sql 2000 you can't but sql 2005 you can by right click database, and click Files, for the owner

celestia SSC Veteran Points: 233 More actions · Answer 10

celestia

SSC Veteran

Points: 233

January 15, 2009 at 8:41 am

#927404

Thank you Tracey.

aali_online SSC Veteran Points: 221 More actions · Answer 11

To be a little clearer, if you are changing the owner through SSMS the sequence is

Right-click on the database

Select properties

Select files

You will see the owner appear in a field which can be edited.

Jerry Hung SSChampion Points: 12968 More actions · Answer 12

+1 for above

You can do the same thing (change owner) to a SQL Agent Job as well

as sometimes it won't run without the "correct" owner who has permission

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

sureshbabuv Grasshopper Points: 14 More actions · Answer 13

It just like users and group concept in any operating system. db_owner is like administrator who deploy do anything in favour of db, even could delete add or modify

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 14

From BO:

If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database.

Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.

MJ