Assigning DBA Rights in SQL Server

By: Jeremy Kadlec   |   Updated: 2007-07-10   |   Comments   |   Related: 1 | 2 | 3 | > 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:
  • 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
  • Additional Information:
    • When not to use the sa password
    • Who is logging in as the 'sa' login?
    • When was the last time the sa password changed?
    • Password management options for the sa login
    • Auditing your SQL Server database and server permissions
    • SQL Server 2005 Exposed - New DDL Auditing Capabilities
• 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:
    • Service Account Privileges
• 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:
    • <-- SQL Server Security Note --> BUILTIN\Administrators Group
• 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:
    • Giving and removing permissions in SQL Server
• 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.
  • Additional Information:
    • SQL Server 2005 Login Properties - Enforce password policy + Enforce password expiration
    • <-- Security Bulletin --> How do I enforce SQL Server 2000 password changes?
• 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 [email protected] to share them with the rest of the community.

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips