How Application Roles Work in SQL Server

One very interesting security technique available in SQL Server is the application role. It’s an interesting way of applying security to a user, and perhaps a way of preventing users from accessing data with unauthorized applications.

An application role is a role just like any other role. It has a default schema, and it can contain specific rights on securables. Below you can see I have an application role that has rights to a specific table in my database. In this case, at the bottom you can see this table has SELECT rights.


I could grant other rights, but in this case I haven’t.

How is this different than a normal role? On the “General” page of this dialog, you can see something different.


In a normal (database) role, there are members on this page. Here we just have schemas and a password. This password is what gives you the power of this security feature.

If I connect as a normal user to my database and issue a SELECT, I get this:


This user has no rights in this database for this table. However if I invoke the application role, I can select from the table.


Application roles are assigned to a user when the user executes sp_setapprole, with the role name and the appropriate password. Previous permissions are removed, and these permissions are granted.

This can be very handy, as assigning a user rights to connect to an instance, but no rights in a database allows them to connect, but not perform any data manipulation. This means a user connecting from Access, Excel, etc. cannot work with data.

If the user connects with an application that is configured to execute sp_setapprole after the connection, if there are rights for DML assigned to that application role, the user can perform the data manipulation needed for users.

It’s not a perfect security mechanism, and it requires the password to be kept secret along with application programming, but it can be a good way to prevent users from working with your data outside of an application.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

4 Responses to How Application Roles Work in SQL Server

  1. Is this password the only difference between application roles and impersonation? EXECUTE AS USER = ‘MyAppUser’ for example.


  2. way0utwest says:

    That’s a great question. I’ll have to think on it more, but I suppose if you removed all rights (outside of connect) from a user, and then gave them a role that only used stored procs compiled with EXECUTE AS, it would be the same. However administratively that is a nightmare (to me). It feels like it reduces some flexibility, but I need to look at the EXECUTE AS more.


    • I was thinking more in terms of setting up a user with a very strong password (that no one knows). Grant that user whatever permissions are needed. Then once the application connects run EXECUTE AS USER = ‘PermissionsUser’ WITH NO REVERT.


  3. way0utwest says:

    That’s interesting. I haven’t worked enough with that to know if that provides the same protection. It would avoid the password issues with app roles, though that’s sometimes a good thing. You might want someone to use that role from Excel.


Comments are closed.