locked
database ownership - sa disabled RRS feed

  • Question

  • 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!!

    Wednesday, March 12, 2014 6:55 PM

Answers

  • 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

    • Proposed as answer by Sofiya Li Thursday, March 13, 2014 1:28 AM
    • Marked as answer by Sofiya Li Friday, March 21, 2014 12:54 AM
    Wednesday, March 12, 2014 10:31 PM
  • 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
    • Proposed as answer by Sofiya Li Thursday, March 13, 2014 1:29 AM
    • Marked as answer by Sofiya Li Friday, March 21, 2014 12:54 AM
    Wednesday, March 12, 2014 10:43 PM
  • 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.


    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

    • Marked as answer by Sofiya Li Friday, March 21, 2014 12:54 AM
    Thursday, March 13, 2014 7:07 PM

All replies

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

    David


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

    • Proposed as answer by Sofiya Li Thursday, March 13, 2014 1:29 AM
    Wednesday, March 12, 2014 7:41 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

    • Proposed as answer by Sofiya Li Thursday, March 13, 2014 1:28 AM
    Wednesday, March 12, 2014 7:51 PM
  • 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

    • Proposed as answer by Sofiya Li Thursday, March 13, 2014 1:28 AM
    • Marked as answer by Sofiya Li Friday, March 21, 2014 12:54 AM
    Wednesday, March 12, 2014 10:31 PM
  • 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
    • Proposed as answer by Sofiya Li Thursday, March 13, 2014 1:29 AM
    • Marked as answer by Sofiya Li Friday, March 21, 2014 12:54 AM
    Wednesday, March 12, 2014 10:43 PM
  • 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 4:07 AM
  • Thanks for the scripts. you recommend using a non-sysadmin disabled login?


    Paula

    Thursday, March 13, 2014 6:57 PM
  • 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.


    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

    • Marked as answer by Sofiya Li Friday, March 21, 2014 12:54 AM
    Thursday, March 13, 2014 7:07 PM