Here's the problem with Application Roles. They are almost exactly the same as everyone using the same SQL Server Login and password, something that is listed as a Worst Practice. The only difference is the user connects to the server under their own credentials (either Windows Authentication or a SQL Login), then switches to the Applicaiton Role's security context (so it is possible that someone has the Application Role and password, but won't be able to connect).
The other downside is the user is interacting with the database under the security context of the Application Role. Any rights assigned to the user's Login or User are completely ignored. This can make security audits and troubleshooting very confusing if the use of Application Roles is not clearly documented and the documentation is accessible.
My question around Application Roles has always been: how is this superior to using user-defined Database Roles and adding AD Users, AD Groups or SQL Users to the user-defined Database Role?
commented on Feb 12 2012 4:39PM