Best practice for SQL server database ownership

Question

Post reply

Best practice for SQL server database ownership

WhiteLotus

SSChampion

Points: 12120
More actions
October 9, 2019 at 12:49 am

#3687219

Hi ,
I am trying to find the best practice for database ownership .
I know many people suggests SA but it has the highest privilege and it opens the security hole
please kindly submit your feedback
thank you

Viewing 9 posts - 1 through 8 (of 8 total)

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

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 1

I set sa as the database owner but I always disable the sa account so there are no problems with security.

Thanks

Brahmanand Shukla SSC Eights! Points: 980 More actions · Answer 2

SA should be disabled in Production environment. There could be another login/user with required roles.

If Windows AD Accounts can be used instead of SQL Server Authentication then it provides additional benefits in terms of security and minimizes the Audit risks and also it indicates which user has which access and what he/she did?

SQL Server Carpenter - Consultant & Trainer
Author of Learn T-SQL from Scratch

WhiteLotus SSChampion Points: 12120 More actions · Answer 3

Thanks for the response

dont you rename sa as well ?

Any problem comes up after disabling sa ?

I plan to create a sql account and give it as sys admin previeliege and set it up as db owner , sa itself will be renamed and disabled

thoughts?

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 4

I don't rename the sa account but you can do if you wish. Disabling the account doesn't create any problems as internal processes are still able to use it.

Make sure you use a secure password for your sa account which is unique to the instance, don't re-use passwords.

Thanks

Brahmanand Shukla SSC Eights! Points: 980 More actions · Answer 5

That's another best practice to rename the "sa" account. You should do it if you can.

There should not be any problem as far as you have some another account with required privileges to carry out your duties 🙂

SQL Server Carpenter - Consultant & Trainer
Author of Learn T-SQL from Scratch

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 6

NorthernSoul wrote:

I set sa as the database owner but I always disable the sa account so there are no problems with security.
Thanks

Same: I also rename the sa account.

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 7

Create specific logins as owners for the databases. Just as in the other question you asked, least privilege principle should always apply. Setting 'sa' as owner potentially opens things up because then, that database owner is also the database owner of all the other databases. No matter how secure that login may be, if you can get to one database, you can get to all of them. Isolate the ownership of each database and you isolate access to that database from the others. As I said in the other question, this is undoubtedly more work, but it's also more secure.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

Grant Fritchey wrote:

Create specific logins as owners for the databases. Just as in the other question you asked, least privilege principle should always apply. Setting 'sa' as owner potentially opens things up because then, that database owner is also the database owner of all the other databases. No matter how secure that login may be, if you can get to one database, you can get to all of them. Isolate the ownership of each database and you isolate access to that database from the others. As I said in the other question, this is undoubtedly more work, but it's also more secure.

I could be wrong but I don't believe that is true unless the current database has been set to "trustworthy".

I do, however, totally agree that there should be a "service login" for each database that no individual uses directly.

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