Application Roles: Permissions That Follow the App, Not the User

Application roles let permissions follow the application, not the user. The role has its own rights and its own password. Whoever knows that password can switch into it.

Why application roles exist

A developer once told me, “We use an application role, so only our app can read that table.” It sounds right. The role has the permissions, and the users have none. But look closely at what the role actually checks. It checks a password. It never checks which program is on the other end.

Let me show you with a small demo. We will make a plain user, a role, and two tables, one for each.

Set up a user, a role and two tables

ReportUser can read UserOnlyData. The application role OrderApp can read AppData. Nobody gets both. The demo uses its own database, SqlAuthorityDemo, and drops it at the end.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
USE SqlAuthorityDemo;
GO
CREATE TABLE dbo.UserOnlyData (Id int PRIMARY KEY, Note nvarchar(100));
CREATE TABLE dbo.AppData (Id int PRIMARY KEY, ValueText nvarchar(100));
INSERT dbo.UserOnlyData VALUES (1, N'visible to the user');
INSERT dbo.AppData VALUES (1, N'protected');

CREATE USER ReportUser WITHOUT LOGIN;
GRANT SELECT ON dbo.UserOnlyData TO ReportUser;

CREATE APPLICATION ROLE OrderApp WITH PASSWORD = N'Str0ng!Demo#Pass1';
GRANT SELECT ON dbo.AppData TO OrderApp;

What the plain user can do

Now act as ReportUser. The first query shows who we are. The second reads the table this user has rights on. The third tries the application table.

EXECUTE AS USER = 'ReportUser';

SELECT USER_NAME() AS CurrentUser;
SELECT Id, Note FROM dbo.UserOnlyData;
SELECT Id, ValueText FROM dbo.AppData;

The result says ReportUser, and the first table returns its row. The AppData query fails with error 229, permission denied. That is exactly what we want from a user with no rights there.

Switch into the application role

Activation uses sp_setapprole. First, the wrong password. SQL Server answers with error 15161 and tells you the role does not exist or the password is wrong. It does not say which, which is a kind touch.

EXEC sys.sp_setapprole @rolename = N'OrderApp', @password = N'wrong password';

Now the right password. I ask for a cookie, because the cookie is the only way back to the original user. Watch the identity change.

DECLARE @cookie varbinary(8000);

EXEC sys.sp_setapprole @rolename = N'OrderApp',
     @password = N'Str0ng!Demo#Pass1',
     @fCreateCookie = 1, @cookie = @cookie OUTPUT;

SELECT USER_NAME() AS CurrentUser;
SELECT Id, ValueText FROM dbo.AppData;
SELECT Id, Note FROM dbo.UserOnlyData;

EXEC sys.sp_unsetapprole @cookie = @cookie;
SELECT USER_NAME() AS UserAfterUnset;

The current user is now OrderApp. The AppData row comes back. But the query on UserOnlyData fails with error 229. The connection gave up everything ReportUser had and took only what the role has. That is what “permissions follow the app” means.

After sp_unsetapprole with the cookie, the user is ReportUser again.

The password is the whole gate

Here is the part that surprises people. ReportUser is not an application. It is a plain user who knew the password. SQL Server was happy to switch it into the role. Any tool that can send the password can do the same, including a query window on someone’s laptop.

So treat the password like a key. Keep it in a secure place, not in source control. Give the role only the rights it needs. If you want real proof of who is connecting, an application role alone will not give you that.

Also plan for the failure path. Connection pools reuse connections, so a request that stops halfway should restore the context with the cookie before the connection goes back. Test that case, not just the happy one.

Clean up

The last block reverts to the original user and drops the demo database, with its user, role and tables.

REVERT;
USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

The script prints three errors on purpose: the two 229 denials and the wrong password.

The next time someone says only the app can reach a table, ask how well the password is kept.

An application role is not a proof of the application, it is a password that grants rights.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.