>>If you are using Windows forms with a two tier physical setup, I don't see why you can't use an application role.
>
>If you read the entire thread you would know that I'm using Mere Mortal.NET and it seems like the way MM.NET managed connections cause problem with using application role. BTW, this is a WinForm application.
>
>MM.NET uses connection pooling so it would open and close each connection each time. Do you know if application role should work in this situation?

Yes, there should be some type of event or place where you can add code to set the app role each time a connection is made.

Not sure how pooling comes into play on a single user app like winforms. I think Mike Levy told me once that the connection retains the app role with pooling even after the app closes the connection. If this is the case, on making the connection you can check to see who the user is. I am pretty sure one of the function returned the app role name.

Yes, I just tested, SELECT user_name() returns the approlename if it is invoked. So, psuedo code would be...

* In after connection made event in MM or overridden connect method whatever
SELECT user_name()

if user_name() not == 'youraprolename'
set the connection to the approle sp_setapprole 'aprolename','password'

return