Using SQL Server application roles

Learn how to use application roles in SQL Server and some things to consider ahead of time.

SQL Server provides a means to set group permissions based on logical roles. There are server roles and database...

roles; some are built-in and standard; but one role, the application role is underutilized and can be valuable. Roles can also be either standard or user defined, public or private, as well. All SQL Server roles require that the user be validated first using the Windows authentication system, regardless of the responsibility and access that SQL Server provides them; that is, the user must be known. There's even a low level user role that restricts a user from anything other than SELECT data access.

The application role provides a means to designate any role to a user on a temporary basis, and while the application role is active the user's normal role and privileges is suspended. This process is a similar to opening a command window in UNIX or using the Run dialog box to designate that the user is either a superuser or an administrator. The Application role can be similarly made password protected, as compared to any user defined standard role which is defined and cannot be password protected. Application roles like other standard roles are both user defined and specific to the database they were defined in.

Since an application role is database specific, that database must be in use for the role to be active. When the connection to SQL Server is broken the application role is by default terminated. To protect application roles, it is not possible to activate that role using database logic in a stored procedure or using a user defined transaction.

You want to consider using an application role when you have an operation where something sensitive must be done and you need to limit access. Suppose you have a medical database with strictly confidential records that must be accessed and modified by a select few. That requires a query and giving SELECT, INSERT, DELETE, and UPDATE privileges to that group of users. If you have a front-end database and allow all users to so modify data, then all of your application logic will be circumvented. When you create an application role only those people who can log on successfully can perform these functions. You can program your database to activate the application role based on selected conditions. Therefore, an application role will allow you to avoid things like shadow logins or special security code.

Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning