Gaining an Advantage with Application Roles

You’ve seen them, but have you really ever used them?

• By Eric Johnson
• 10/08/2008

Before we begin, though, let's look at how application security can be implemented in SQL Server. There's debate over which model is better and I am not endorsing any specific one. Regardless of whether you use SQL Server Logins or Windows Authentication, you still have to decide whether an application uses a single login to access SQL Server (and all appropriate database objects), or allow users to have their own login.

Whichever approach you take, each has its pros and cons. But let's look at the disadvantage to users having their own login. The biggest issue is that each user login has access to your server and to one or more databases. Does the user need to delete data as part of his job? If so, they'll have this right whether they log in via an application or directly to the server. Often, the application controls what can and cannot be deleted based on a set of business rules; these rules usually don’t exist on the SQL Server. In short, if each user has his or her own login, they can access SQL Server directly and potentially cause damage.

This brings us to application roles. You create them and assign permissions to them just like regular database roles, but you can’t put users in them.

Application roles provide the best scenario for application and user security. Here’s how it works: You set up each user with an account on the SQL Server with practically no rights. All they should be able to do is login to the server and run a system stored procedure called sp_setapprole. This procedure accepts a couple parameters, including name and password for the application role. Running sp_setapprole will immediately endow the user with all the permissions that you set up on the application role for the current session only.

What does this mean for security? As long as the password for activating the application role is only known to the application, your users will not have any rights when they login to the SQL Server directly. In order to have the permissions they need, they will be required to use the application that knows the password and can unlock the permission for the application role. Now you can have SQL Server manage individual logins and still have a secure environment that uses the rules and filters in place within your applications.