April 2, 2019 at 9:08 pm
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?
April 2, 2019 at 9:33 pm
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
April 2, 2019 at 10:16 pm
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
Change is inevitable... Change for the better is not.
April 3, 2019 at 5:31 am
I have an SQL Agent job that checks the db owner and changes it to sa if it is anything else.
...
April 3, 2019 at 10:24 pm
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
Change is inevitable... Change for the better is not.
April 4, 2019 at 3:22 pm
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.
April 4, 2019 at 3:29 pm
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).
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply