What if I told you that you could sign an assembly that your Windows application uses with a certificate, load that certificate into SQL Server and then define rights on that user-assembly combination? The perfect world where a user would have different rights based on the application they are running but still act under their Windows identity. The perfect world where things work just the way they should.
Sounds pretty awesome, huh?
The problem is that, as far as I know, this functionality does not exist yet. There might even be very good reasons why it does not exist yet, but this is a feature that I believe is overdue. Because of this, I have opened a Connect item to try to get Certificate Based Application Roles built into SQL Server. Here is the text of my Connect Item:
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.
If you are like me and think this functionality should exist then please go vote up my Connect Item.
