database ownership - sa disabled

Article
03/12/2014

Question

_{Wednesday, March 12, 2014 6:55 PM}

Who should own the dbs if sa is disabled on a NON ISV environment?

Currently some dbs are owned by an orphaned user and others owned by sa that was disabled.

Thanks!!

All replies (7)

_{Wednesday, March 12, 2014 10:31 PM ✅Answered | 1 vote}

Let us be a bit careful with the word "trust".

If the database is configured to be "trustworthy", having it being owned by sa makes it possible to elevate privileges from inside the database.

This is sometimes not intended when using that property.

So to be on the secure side I do recommend using a non-sysadmin (diasabled) Login.

The "problem" with that is, you have to make sure for all server where you restore that database/failover to, that this login exists already. But that's not an exclusive demand and actually necessary for many other scenarios unless contained databases are being used. So that's not hard to take care of, if that is made a policy.

Feel free to download my security-check script and take part in the survey on this exact topic: Security-Check-Script & Survey: SQL Server Security - Database-Owners, critical Permissions and role membership

I will hopefully manage to publish my recommendations in a bit more detail next month or so..

Andreas Wolter (Blog | Twitter)
MCM - Microsoft Certified Master SQL Server 2008
MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.andreas-wolter.com | www.SarpedonQualityLab.com

_{Wednesday, March 12, 2014 10:43 PM ✅Answered}

For maximum security, I would recommend that each database has its own owner whose sole raison d'être is own that database.

It is not insecure per se to have sa as the database owner, but if someone careless makes a database trustworthy that can open doors.

If you're interested, see here for a discussion:
http://www.sommarskog.se/grantperm.html#TRUSTWORTHY

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

_{Thursday, March 13, 2014 7:07 PM ✅Answered}

Thanks for the scripts. you recommend using a non-sysadmin disabled login?

Paula

Right.

Even better if you create several Logins like Erland also stated. Maybe at least groups of them.

I know this is a lot of work, but this is the maximum security.

You are already better off, if you don't use sa/sysadmin.

_{Wednesday, March 12, 2014 7:41 PM}

If you trust the databases, sa. It doesn't matter if sa is disabled.

David

David http://blogs.msdn.com/b/dbrowne/

_{Wednesday, March 12, 2014 7:51 PM}

In some instance the application need some specific user to be owner of the database.Apart from such scenarios there won't be any issue with either sa or any other user owning the database.
We need to ensure that who is accessing and what and all operation he is performing on the database matters a lot.

-Prashanth

_{Thursday, March 13, 2014 4:07 AM}

Additionally the instance owner should trust anyone who can create stored procedures and triggers, as these run in the security context of the caller. And there are many possible attacks based on inducing a privileged user to to run a malicious bit of code.

David

David http://blogs.msdn.com/b/dbrowne/

_{Thursday, March 13, 2014 6:57 PM}

Thanks for the scripts. you recommend using a non-sysadmin disabled login?

Paula

Share via

database ownership - sa disabled

Question

All replies (7)

Additional resources