Application roles provide a practical way to assign application-specific permissions in your database and to make sure that your applications always use a defined login. Not to be confused with actual roles, application roles are more like users in the database.

How it works

To assume the permissions of an application role, you first need to establish a “regular” connection to the database. The typical scenario for application roles are where an application requires more privileges in the database than the user has. An application role, in terms of security design, is not really a user but a role, in the sense that it cannot be a member of a database role.

Here’s the typical login/logout flow of an application role:

• The user connects to the database
• The user assumes the identity of the application role
• The user can now perform tasks using the security context of the application role, not the original user.
• The user “unsets” the application role, and reverts to the original user identity
• The user disconnects from the database.

Security context

As opposed to regular logins, application roles are database-specific, so the application role security context cannot access other databases on the server (except as a guest user). Also, because of this, the application role does not allow permissions to system-wide metadata, because this info is stored in the master database.

Setting up an application role

Creating an application role is really straight-forward.

CREATE APPLICATION ROLE {rolename} WITH PASSWORD='password';

Assuming the database role identity

Once logged in as a regular user, use the procedure sp_setapprole to invoke the application role.

EXECUTE dbo.sp_setapprole 'myAppRole', 'myPassword';

This application role now remains for the duration of the connection. If you want to be able to “unset” the application role and revert to your regular user security context, you’ll have to remember a so-called cookie, in the form of a varbinary.

DECLARE @role_cookie varbinary(8000);

EXECUTE dbo.sp_setapprole 'myAppRole', 'myPassword',
    @fCreateCookie=true, @cookie=@role_cookie OUTPUT;

To unset the application role, the sp_unsetapprole procedure is used:

EXECUTE dbo.sp_unsetapprole @role_cookie;

To verify which user or application role you are currently using, use the following:

SELECT SUSER_SNAME();

Permission assignment to application roles work just like with regular users, using the T-SQL GRANT statement.

Deleting an application role

As with anything that has a CREATE statement, there’s a DROP statement for application roles.

DROP APPLICATION ROLE myAppRole;