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.

4 thoughts on “How Application Roles Work in SQL Server

  1. 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.

    1. 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.

  2. 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 now closed.