SQL Server Security In-Depth: System Administrators

Let's take a good look at SQL Server security, R2-style.

• By Joshua Jones
• 05/10/2010

In my next several posts, I'd like to take a good look at SQL Server security. We'll primarily focus on SQL Server 2008 R2, which is the latest release. Many of the concepts I plan to discuss will apply across most installations of SQL Server regardless of version, but some functionality will vary based on the version you are working with. We'll also talk about the evolution of security from version to version, and shed some light on the newer features that make overall security schemes a little better.

First up, let's look at the crucial "system administrator" functional role. Most of us know what the SQL Server login 'sa' is for; it is a built-in, local login that automatically has all of the rights possible to a SQL Server. It is, for lack of a better phrase, the "omnipotent" account. When SQL Server is installed, this account always exists. However, if SQL Server was installed with the security mode set to "Windows Authentication," the account is disabled by default. If "Mixed Mode" authentication is selected, the installer asks for a password; this is the password for the 'sa' account.

In addition to the 'sa' account, SQL Server 2008 and later versions asks the installer (if the wizard is being used) to add any accounts that should be administrators. Most people will add their own account, or may have specific domain accounts to add here. After installation, these accounts will automatically be administrators.

All of these various accounts get their administrative abilities via the SQL Server Fixed Server Role "sysadmin". After installation, DBAs can manage system-wide administrators by removing or adding logins to this role as needed.

However, just because you can give these rights doesn't mean you should. For most installations, the sysadmin role should only contain the 'sa' login, and ideally, a domain group that contains only those logins that MUST be able to make server wide configuration changes. Many organizations don't even let all of their DBAs be members of this group; only senior level DBAs. The other fixed server roles can be used for specific configuration management. I won't list them all (Books Online has all the details), but I will suggest that most shops make use of the securityadmin and dbcreator roles for junior DBAs or developers on development boxes. Otherwise, very detailed consideration should be given to any other user or application login belonging to a server-level security role.

When it comes to development and test servers, a more lenient stance can and should be taken compared to production systems. Often times, particularly in small shops, developers need to be able to do things like run Profiler traces or add/destroy databases, logins, credentials or other server level objects. Most of these permissions can be granted on a per account level WITHOUT using a server role. Often times it's a good idea to use Windows domain groups, and grant those groups access to the server, with the appropriate permissions added. This way, membership in the group can be managed in Active Directory, and permissions on the SQL Server will remain fairly static. You can then add multiple domain groups, with groups associated with specific types of permissions (reading server configuration, making database changes, etc.).

Finally, many environments also choose to grant two different accounts to IT personnel; one is a "user" account, which has access to e-mail, file servers and the like, while the other is an "administrator" account that has the correct privileges on servers being managed. Be sure to define a clear policy for these logins inside of SQL Server as well. And, as with any configuration, document everything for both audit purposes and future deployments.

In my next post, we'll discuss how to separate application security from user security, and outline some of the security features applications can use inside of SQL Server. Until then, have fun!