Skip to content

How Application Roles Work in SQL Server

November 26, 2012

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.

approle2

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.

approle1

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:

approle3

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

approle4

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 these ads

From → Blog

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

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

Follow

Get every new post delivered to your Inbox.

Join 4,297 other followers

%d bloggers like this: