Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

Assigning DBA Rights in SQL Server

MSSQLTips author Jeremy Kadlec By:   |   Read Comments   |   Related Tips: 1 | 2 | 3 | More > Security

Problem
As a DBA you have the responsibility to ensure the security for the application and users related to the SQL Servers that you manage.  But what are the security principals that you follow for the administrators?  Are there any hard and fast rules that must be followed?  What concerns should be taken into consideration when the rights are assigned to DBAs?  How is this any different for a large organization versus a small team where many of the team members wear numerous hats (DBA, Developer, Network Admin, System Admin, etc.)?

Solution
What is really necessary is to have the right balance based on your environment and industry.   However, too lackadaisical of an approach and you may be opening yourself up to unnecessary vulnerabilities with the accounts that are probably the most common target due to the depth and breadth of rights.  With security too stringent this may limit folks from being able to do there jobs leading to frustration and dissention in the ranks.  The reality is there are a few different ways to approach security for DBAs and administrators in general.  Depending on the organization and security requirements in the specific industry, the administrative rights may be different and acceptable.  Finally, the reality is that someone (or some people) need to have the keys (or access) to the kingdom.  This information and responsibility comes with accountability that must be handled appropriately.

With all of this being said, there are security principals that should be followed just by the nature of serving your organization in an IT administrative capacity.  Ironically, I have seen a number of these 'best practices' violated lately.  As such, below outlines a number of recommendations to consider for administrative access to SQL Server based on recent observations:

  • Do not write passwords on sticky notes and place them on your monitor.  Just because you cannot remember a 15 character password that someone else created does not mean that someone else cannot look over your shoulder and write it down.  Instead, consider the following:
    • A physical or electronic password safe to securely store the passwords
    • Migrate all SQL Server authentication to Windows based authentication where you only need to remember a single password
    • Do not use the sa password unless you have a very specific need that cannot be fulfilled with another account
  • Do not create SQL Server standard logins on all of the SQL Servers with SQL Server system administrator rights rather than using the sa login.  Instead, consider the following:
    • Migrate all SQL Server authentication to Windows based authentication where you only need to remember a single password
  • Do not login with the sa login and password to all of your SQL Servers because auditing the sa login with a shared password is difficult without capturing the host name or some other personally identifiable data.  Instead, consider the following:
  • Do not setup the DBA's Windows account or the SQL Server service accounts as Domain Administrator account.  Instead, consider the following:
    • For the DBA account a Windows user account is really all that is needed.  Individual rights can be granted at each SQL Server (machine) if those rights need to be elevated.  If the DBA is also the network admin consider two separate accounts to perform user type activities with one and administrative type activities with the other.
    • For the SQL Server service account, Domain Administrator rights are not needed and should not be considered a short cut or easing the administrative burden as opposed to granting rights to individual SQL Servers (machine) and network shares.  It is easy enough to grant those rights once or if this is a complicated process or a mess, consider re-engineering the process to make it more simple.
    • Additional Information:
  • Do not let the DBAs or System Administrators rely on the BUILTIN\Administrators group for access to the SQL Server because if the local administrators group is compromised then the those individuals would have SQL Server system administrator rights in SQL Server.  Instead, consider the following:
    • Setup one or more Windows groups with the needed accounts in each group.  Then grant rights to those groups in SQL Server.  As individuals are added or removed from the team, they can just be added to those groups, simplifying the overall account management.
    • Either drop the BUILTIN\Administrators group or remove the System Administrator rights once the necessary groups Windows groups are in place and those groups have been assigned System Administrator rights in SQL Server.
    • Additional Information:
  • Do not unnecessarily grant SQL Server rights to Windows groups or specific logins especially the system administrator rights in SQL Server.  Instead, consider the following:
    • Really understand the rights that are needed and grant only those rights. 
    • Start off granting the least amount of rights and increase the rights until all of the needs are met.
    • Be aware of the limitations and functionality with SQL Server 2000 versus SQL Server 2005.  SQL Server 2005's security is an order of magnitude more granular than SQL Server 2000 potentially justifying an upgrade in a environment where security is a core business requirement.
    • Additional Information:
  • Depending on the local Windows resources needed grant either explicit rights to the SQL Server related directories needed or grant local administrator rights to the Windows DBA group.  The granularity of rights really depends on the organization.
  • In SQL Server 2005 leverage the password policies from Windows that can be applied to your SQL Server standard logins when those logins are needed to support an application.  Just keep in mind that you will need to have a strong password and the password will need to be changed on a regular basis.
  • To come full circle, if you cannot remember that 15 character password from the first bullet, consider a pass phrase versus a password.  Hopefully that will help jog your memory and protect an account with elevated privileges.

Next Steps

  • Take a good hard look at your SQL Server security for the DBAs and/or administrative users to determine if the security is acceptable or not.
  • Determine if any of the items from this tip should be included in your environment and what changes would be necessary.
  • Check out the following Security and Compliance categories on MSSQLTips for additional tips.
  • If you have more do's and do not's when it comes to SQL Server security in your environment, send them to tips@mssqltips.com to share them with the rest of the community.


Last Update: 7/10/2007


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.