3

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.

markltx
  • 689
  • 8
  • 16

3 Answers3

5

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;
gbn
  • 422,506
  • 82
  • 585
  • 676
4

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.

Ed Harper
  • 21,127
  • 4
  • 54
  • 80
0

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.

user10777295
  • 1