I am in search of a more perfect application role. What I would like is to be able to sign a Windows executeable or even an individual assembly with a certificate. I would then take that certificate and load it into SQL Server. I would then associate the certificate to Windows users and groups to form an application role.
The benefit of this approach is that depending on the executeable a user is running they could have different rights. A user that has reader on all tables in a database to query with Access might have execute on certain procedures when running the accounting application but have update on other tables when running the payroll software.
If the user changes departments then they would change rights via group membership. If they leave the company then all rights would be removed like any other windows login. All activity would take place under the context of the users login, simplifying auditing.