I'm building an application using C# windows form and SQLExpress2008
As for the security I decided to set some application roles.
I read few articles about what they are and how they work but honestly didn't get a thing!
As I realized the application role is active as long as the application is connected via an open sqlconnection. So the moment the connection which executes sp_setapprole is closed the role itself seems to be lost. At this point granted permissions are revoked and it throws an unhandled sql exception saying "Select permission is denied".
Do I have to simply let the connection be after I've executed sp_seapprole procedure? Can any one please get me out of the dark please!