Professional SQL Server™ 2005 Programming

22.5. Application Roles

Application roles are something of a different animal than are database and server roles. Indeed, the fact that the term role is used would make you think that they are closely related—they aren't.

Application roles are really much more like a security alias for the user. Application roles allow you to define an access list (made up of individual rights or groupings of databases). They are also similar to a user in that they have their own password. They are, however, different from a user login because they cannot "log in" as such—a user account must first log in, then he or she can activate the application role.

So what do we need application roles for? For applications—what else? Time and time again, you'll run into the situation where you would like a user to have a separate set of rights depending on under what context he or she is accessing the database. With an application role, you can do things like grant users no more than read-only access to the database (SELECT statements only), but still allow them to modify data when they do so within the confines of your application.

Note that application roles are a one-way trip—that is, once you've established an application role as being active for a given connection, you can't go back to the user's own security settings for that connection. In order for users to go back to their own security information, they must terminate the connection and log in again.

The process works like this:

1. The user logs in ...