Is there any reason not to use [sa] as db_owner?
Question
Saturday, April 5, 2014 7:15 AM
Hi,
Is there any advantage for not using [SA] as the owner of database ?
If we create a SQL Server Login [DatabaseA_Owner] as owner of the Database_A, is it necessary to create a user [DatabaseA_Owner] under that database and map it ?
Thanks
All replies (6)
Saturday, April 5, 2014 7:23 AM ✅Answered
Blog: Blitz Result: Database Owners <> SA
Kalman Toth Database & OLAP Architect Free T-SQL Scripts
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012
Saturday, April 5, 2014 7:40 AM ✅Answered
Hi,
Is there any advantage for not using [SA] as the owner of database ?
In my opinion you should never use the sa account as the owner, moreover, do not use it for anything that can be done with other user! In one sentence, the reason is the same to not using the Administrator account to play a computer game on the server (yes some of us do it).
If we create a SQL Server Login [DatabaseA_Owner] as owner of the Database_A, is it necessary to create a user [DatabaseA_Owner] under that database and map it ?
Thanks
no. it is not necessary. Everything depend on your needs and system.
[Personal Site] [Blog] [Facebook]
Saturday, April 5, 2014 8:52 AM ✅Answered
I do think that using sa login as database and jobs owners is a good and secure thing. What if the a person whom login that owns the database was leaving the company and you don't dare to remove that login/Windows account. We can of course create some SQL login or Windows login especially for this purpose and use that. But we have sa login already there. Moreover sa always has the same sid number (makes moving databases across instances a bit easier).
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
Saturday, April 5, 2014 9:54 AM ✅Answered
Hello,
As a best practice you should always follow principal of lest privilege.Think what could you possibly attain by making SA as owner of a database if you have some pretty good reasons and restrictions then go ahead( i have seen in some environment) but if you are just keeping it for sake that lets make SA as DB owner then that is wrong practice.In any database world always provide just the rights which are necessary and sufficient to perform the activity.
Generally whoso ever creates the database becomes its owner.So by default he would have rights on that database so why to change it,if anybody else requires access mp his login with requires credential to database.Uri pointed out good example .
Tibor has also blogged about the same below
Using SA as owner of jobs and database
On the contrary all system databases have there owner as SA which you cannot change .So had that been so bad why would MS put default owner as SA.
Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers
Thursday, April 10, 2014 2:50 PM
Hi Uri
We do not have to agree :-), and seem that in this issue we absolutely do not agree. I have seen lots of situations, where people got too much permissions and made to much mistakes. Theoretically you can secure any system and any user (not 100%), but why do you build a security window (Security breach), only in order to close it for ever?!?
Is "esier" what you looking for or "better"/"secure"? And this is esier for those who dont know usually. With one query as you know, we can fix all the orphaned users.
* Dont forget who use the database. Not always the people that work with the datbases know what they do! think on developers for example which need sometimes to use the database owner (again... you can secure anything, and get a DBA to sit near each developer and so on... but if you don't have a window then you don't need to close it)
[Personal Site] [Blog] [Facebook]
Saturday, April 12, 2014 4:12 AM
In addition, have a look at these links:
sysadmin + db_owner + sa + proxy = how to keep DB secure
The Power of the SQL Server Database Owner
sqldevelop.wordpress.com