db owner best practice

Question

db owner best practice

sqlfriend

SSC Guru

Points: 52769
More actions
April 2, 2019 at 9:08 pm

#2548725

When we create a database, the owner is usually is whoever created the database.
I read some suggested right after create the db, change its owner to sa.
Some said no, for SA is an account that have super permissions.
I wonder what is the best practice?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

I like using sa as the database owner. Since sa already has all permissions, you can't accidentally give extra permissions by doing so. It's not escalating any privileges, it's not allowing anyone to log in or execute as sa that couldn't before. It's just saying that this database is owned by the built-in sysadmin account

And it means that you can't end up with an owner-less database if the creating AD account is removed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

I'll strongly second Gail's response. I'm thinking that Brent Ozar would, as well. His sp_Blitz tool checks to see if the database is owned by "SA" or not and makes a recommendation to do so if it is not.

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

HappyGeek SSCoach Points: 18726 More actions · Answer 3

I have an SQL Agent job that checks the db owner and changes it to sa if it is anything else.

...

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

I would make one that reports when the owner is something else but I'm not sure I'd make one do an auto-change. You just can't tell when that could break an application, especially a 3rd party application.

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

jarick 15608 Ten Centuries Points: 1253 More actions · Answer 5

I second Jeff's last tag because some vendors demand to be the owner of a database. DB_OWNER does not cut it because they don't check roles, just who owns the database.

Andrey Right there with Babe Points: 780 More actions · Answer 6

Agree with Jeff - it's not worth to change db owner to SA automatically.

It may affect ex-owner to lose access to the database.

Ex-owner should be created in the database as an user with db_owner role.

But it is not 100% warranty also that application will work fine after that.

I'm not sure if it's applicable to the modern versions of SSMS, but old ones do not show db properties is db owner is missing (login is deleted).