SQL Server Security In-Depth: Users and Developers

Users and developers access SQL Server for completely different reasons. So, be sure you set up access as defined by who they are and why they need access.

• By Joshua Jones
• 05/17/2010

For many database administrators, managing users and applications proves to be the most time-consuming and tedious task. Particularly in large environments, once a SQL Server has been installed the number of users requesting access grows quite fast. Similarly, applications will begin to seemingly fall out of the sky.

And unfortunately, most users and applications (rather, their developers) will ask for more permissions than they actually need to accomplish their goals. The primary focus should be on A) separating users from applications, and B) granting the fewest amount of privileges while allowing work to continue uninterrupted. For now, let's focus on user security (we'll talk about application security next time).

In the vast majority of organizations, users will fall into two categories: data consumers and developers. Data consumers are the largest group, and comprise everything from business analysts to executives, and even other IT personnel. Developers are usually application developers, report writers and database developers. These groups do not need the same permissions.

First, let's go over a quick quick primer on granting users access to your server. There are two types of logins that users (or applications, but we'll get to that later) can use; SQL logins and Windows logins.

SQL logins are accounts that are stored in the SQL Server itself, and consist of a user name and password. This is the simpler of the two authentication types, but less flexible, since the account ONLY exists on a given SQL Server. A good example of a SQL login is the built-in account 'sa', which has system administrator permissions.

A Windows login is actually a login that is associated with a Windows Domain account or Windows Security Group. This means that the account can exist on multiple servers (with different permissions if necessary), and can use advanced authentication methods, such as Kerberos. Windows Security Groups can be mapped to a single Windows login on a SQL Server, meaning that every Active Directory user account that belongs to the Security Group will have the same permissions on a given SQL Server. This allows much greater control, while reducing the overhead of managing user accounts that have identical permissions.

Data consumer should, by and large, be accessing databases via an application. And if so, the application should have a login, and the user should never get explicit rights on the SQL Server. However, there are always exceptions to this rule (i.e. an executive-level manager who wants to be able to query sales data directly using Microsoft Excel). Its best to minimize these users' access, granting only read permissions to tables and views, and executing permissions to the appropriate stored procedures. Even then, consider using database-level roles to create "groups" of permissions based on access to specific schema items. This can have some affect on development, so be sure developers in your environment are being cognizant of how schemas and credentials will work together.

Developers will have varying levels of permissions on SQL Servers. If the environment is large enough to support Development, Test, and Production servers, they'll have different permissions on each of these environments.

In the development server, most developers will need the rights to be able to create and destroy all schema objects in their respective databases, as well as manipulate any data. They may also need to manage security (to create application logins, for example). However, consider restricting them from anything that needs to be modified at the server level; viewing the server state should be OK. In the test environment, security should be as close to production as possible. This means that most developers will have read access only, so as to prevent changes from being made in the test environment before they've gone in to the development environment. Again, use of domain groups and database roles will help keep this all straight.

Finally, and this is more important than most realize, document your security strategy. We'll talk about application security, and documentation, in my next post. Until then, have fun!