Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET

Application roles are designed to implement security for particular applications. They are different from standard database roles in that

• Application roles require passwords to be activated.

• They do not have members. Users access a database via an application. The application contains the name of the role and its password.

• SQL Server ignores all other user permissions when the application role is activated.

To create an application role, administrators should use sp_addapprole:

Exec sp_addapprole Orolename = 'Accounting', @password = 'password'

Permissions are managed using the Grant, Deny, and Revoke statements in the usual manner.

A client application (or a middle-tier object) should first log in to SQL Server in the usual manner and then activate the application role using sp_setapprole:

Exec sp_setapprole Orolename = 'Accounting', @password = 'password'

Now the application will not be able to use permissions granted to its original login/ user, but only the permissions granted to the application role.

The biggest disadvantage to application roles is that they can access objects in other databases only as guest users, which may not be sufficient. For more information, refer to the "Application Roles" section earlier in this chapter.