SQL Server Application Role vs. regular logins and users

3.3k Views Asked by At

What is the advantage of using a SQL Server application role to manage permissions vs. using standard logins/users and granting the necessary permissions to said users?

We have been using application roles which require the following scenario:

  1. Connect to SQL Server using a SQL Server login and password.
  2. Activate an application role by passing the role name and another password to sp_setapprole.

I don't see how that's any better or more secure than just granting the application role's permissions to the login/user. Both passwords must be available to the application and anyone who gains access to the login password can probably gain access to the app role password and call sp_setapprole from their own program or in SSMS. Right?

EDIT: As Ed Harper surmised, all instances of the application use the same login in my scenario.

3

There are 3 best solutions below

1
On BEST ANSWER

It's not completely clear from your description, but it sounds like you might be using a SQL login assigned at application level - i.e. all instances of the application (assuming there is more than one instance) use the same login/password. In that scenario, using an application role adds very little value.

As I understand it, application roles are intended to be used where each user has their own login in SQL Server (perhaps in a scenario where access to the database is granted by AD authentication), but you don't want to grant users the same rights as applications they use; this assumes the application connects to the database with the AD user's identity, then elevates its permissions by using sp_setapprole. I've never seen this approach used in a production system.

0
On

Using a regular login, your credential and password would potentially pass on network. While application role switching could be completed done within database if the password is also saved in the database.

0
On

I've never used application roles (CREATE APPLICATION ROLE).
I really can't see the point of them

I have used database roles and added users as members

CREATE ROLE WebUsers AUTHORIZATION dbo;
ALTER ROLE WebUsers ADD MEMBER Tom;
ALTER ROLE WebUsers ADD MEMBER Dick;
ALTER ROLE WebUsers ADD MEMBER Harry;

CREATE ROLE WebAdmins AUTHORIZATION dbo;
ALTER ROLE WebAdmins ADD MEMBER Tom;

Roles have the permissions, not the user

GRANT EXEC TO WebAdmins;

GRANT EXEC On SCHEMA::WebCode TO WebAdmins;