database owner best practices

Question

Post reply

database owner best practices

C3PO's twin

SSC-Addicted

Points: 445
More actions
January 1, 2008 at 6:23 pm

#182266

I'm just thinking...we have a number of databases created with peoples domain accounts. What happens to the database if they leave the company and their name is removed from AD?
Can anyone tell me what the best practice is for database ownership is? Is it to create them as SA or domain/administrator
thanks..

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

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

I always create them as "dbo" and grant privs... saves a lot of hassle...

--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)

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

Going with dbo as the owner of the db and all the objects in it is might be best here.

when employees leave the company drop the db unless you require it. Atleast unneccessary clutter will be reduced.

"Keep Trying"

C3PO's twin SSC-Addicted Points: 445 More actions · Answer 3

Hmmm, don't follow.

I thought when you created a database you had to use a domain or sql server account. When I type in dbo, I am told it does not exist on this server. By default it picks up my domain account as the owner. Are you saying I have to explicitly create a dbo account to achieve this? - surely not 🙁

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

My bad... didn't say it correctly...

We don't allow individuals to create their own databases... just the DBA's. They're logged in with SA privs so no problems there.

We also require that all objects be explicity named with at least the 2 part naming convention where the owner MUST be "dbo" and each object must have grants either to public, the custom built "Execute_Role" we built, or to "system" login names depending on the nature of the beast and the security required for the object.

--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)

ChiragNS One Orange Chip Points: 26137 More actions · Answer 5

Hi

Since you are using a domain account to login to SQL sever this domain account must have the database owner (dbo) rights in the database.

Generally "sa" or any other system administrator user is most priveilaged user who controls other users.

Make sure that the domain account user has rights only on the required databases and not on others.

"Keep Trying"

george sibbald SSC Guru Points: 104210 More actions · Answer 6

We also only allow DBAs with sysadmin access to create databases (in prod). My personal standard is to immediately change dbowner to 'sa' after creating the database. Bit of a throwback to my sybase days but it keeps things consistent. Also then insist all objects owned by dbo, prevents so much hassle. As long as database owner is a sysadmin all objects created by database owner

will automatically be owned by dbo.

Removing id from AD will not affect database as removing from AD does not remove from SQL. When you try to drop login from SQL you will not be able to as it owns objects (the database) so you will have to change dbowner first at that point. As long as all objects in databases owned by dbo and new owner is sysadmin will be no problem.

HTH

george

---------------------------------------------------------------------

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

As long as database owner is a sysadmin all objects created by database owner will automatically be owned by dbo.

Now, there's a decent tip... 🙂

--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)

george sibbald SSC Guru Points: 104210 More actions · Answer 8

Jeff, if people are actually going to listen to me 🙂 I better clarify my point....

an object created by a sysadmin will always be owned by dbo even when two part naming convention not used.........

Also an object created by the database owner (dbo) in database they OWN will always be owned by dbo even when two part naming convention not used, even if the database owner is not a sysadmin.........

BUT all other users (including members of dbowner group) must use 2 part naming convention else object ownership will default to their id and EVERYONE will have to refer to object by two part name.

So in the context of the post having database owned by a sysadmin with all objects created by a sysadmin will avoid ownership chain problems

---------------------------------------------------------------------

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 9

We made sure that Phantom objects does not get created by going for a two prong security measure.

1. All Objects that need to be created should be Scheama Qualified.

2. No one other than the DBAs have access to the DB. Programmers dont even get read access.

This sure helped us keep tab of the objects created and also made sure that there are no issues in Object ownership.

-Roy

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 10

george sibbald (1/2/2008)
Jeff, if people are actually going to listen to me 🙂 I better clarify my point....
an object created by a sysadmin will always be owned by dbo even when two part naming convention not used.........
Also an object created by the database owner (dbo) in database they OWN will always be owned by dbo even when two part naming convention not used, even if the database owner is not a sysadmin.........
BUT all other users (including members of dbowner group) must use 2 part naming convention else object ownership will default to their id and EVERYONE will have to refer to object by two part name.
So in the context of the post having database owned by a sysadmin with all objects created by a sysadmin will avoid ownership chain problems

Heh... we always listen, George... I think that's all about 250,000 members of this forum ever do is listen (and that's OK! "Lurkers" are welcome here!).

Anyway, I knew what you meant, but now that you've mentioned it, I think anyone who reads this thread will certainly appreciate the time you spent on the clarification. Thanks.

--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)

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 11

What is recommended in the case where multiple independent dbs owned by unrelated individuals/groups/organizations are sharing the same server, and providing local DBA functions themselves, and wish their dbs to remain private and segregated?

george sibbald SSC Guru Points: 104210 More actions · Answer 12

Jim Russell (1/3/2008)
What is recommended in the case where multiple independent dbs owned by unrelated individuals/groups/organizations are sharing the same server, and providing local DBA functions themselves, and wish their dbs to remain private and segregated?

Jim,

I don't work for a hosting company so I don't say this is an industry recommendation, but if these are totally independent dbs and organisations with their own dbas the only way to get true separation is to give each organisation its own SQL instance.

Otherwise DBAs will have to be limited to DBO of their own databases and perhaps create db and manage logins rights, if they get sysadmin rights a DBA would normally expect they can intrude on everyone else's databases. In this case you should have one controlling DBA with sysadmin rights to do all the stuff the DBAs working for the companies renting space cannot do.

SQL 2000 you can have up to 16 instances per server, in SQL 2005 thats gone up, to I think about 50 (can't remember). You will be need a BIG server, loads of memory, possibly 64 bit and lots of drives on lots of spindles for true separation.

---------------------------------------------------------------------

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 13

Jim Russell-390299

SSCrazy Eights

Points: 9333

January 3, 2008 at 5:18 am

#764325

Good points George, thanks!

homebrew01 SSC Guru Points: 55539 More actions · Answer 14

Jim Russell (1/3/2008)
What is recommended in the case where multiple independent dbs owned by unrelated individuals/groups/organizations are sharing the same server, and providing local DBA functions themselves, and wish their dbs to remain private and segregated?

Probably a good opportunity for VMWare