Using Application roles and security - SQL Server 2005

In SQL Server, you can create database roles for easier administration of permissions in a database. Instead of granting individual permissions to each user separately, you can group users with the same permission needs by making them members of the same regular database role, and then assigning permissions to the database role itself. Unless a specific permission is explicitly denied elsewhere, member users will acquire the permissions granted to that database role.

Application roles, unlike regular database roles, do not have members themselves. Instead, users log on to a SQL Server and connect to a database using their own credentials. At that point, the security context of an application role can be applied programmatically to an existing connection by using the sp_setapprole stored procedure.

If you are kind of new in using Application then it is required to test more on Pros and Cons as the application will be used by a few different users, each on a different computer. For instance the application calls stored procedures, updates\inserts records in tables on the SQL and delete rows, in this case you have to be careful about security in using Application Roles, limit the activity with specific actions.

So for these specific security tasks the permissions has to be based on the granularity, say on EXECUTE permissions on Stored Procedures, here you have very useful white-paper  SecurityConsideration-ApplicatioRoles in MSDN.