I was recently having a discussion with a client and they were looking for recommendations on how to handle a common security issue with SQL Server. Here is there situation, they have a number of applications that connect to the database, and security was implemented by using Windows Authentication. The application required read and write access directly to the tables so this required that each of the users had direct read/write access to each of the tables in the database (starting to feel uneasy yet). It didn’t take long before people were connecting directly to the database to start making changes outside the application, or pull reports of their own. Even if the data was not sensitive, and it was… making changes outside the application created all sorts of problems.
Then answer was pretty simple and came in the form of application roles.
Access can be granted to the individual users either by each account or as a group. The permissions can be limited to allow for log on only. Once that has been completed the application role can be created using the Create Application Role syntax. Permissions can be granted accordingly to the application role that allow for the direct table access.
When this is done and someone logs on via the application the application can issue the sp_setapprole and that connection can now use the elevated permissions. If the user were to try to connect to the database without the application role the elevated permissions are not there unless they have the password for the app role and set that role for that connection.
So in other words the application becomes only access method to the data as long as permissions are not granted elsewhere to the individuals user account. This is a great way to take advantage of everything that AS has to offer in the way of account and permissions management without having to worry about opening the database up.