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.