My conclussions (see below section for the why):
Using SQL application roles doesn't plays well with connection pooling and also shouldn't be used directly in final user apps (only on a business tier).
The SQL alternative would take away a lot of advantages from using linq, as it relies in SPs.
My recommendation:
If you have a business tier/server, do authorization at the application level instead of relying on sql authorization. If you still want to authorize, have an account associated to the business tier application, and associate a normal role to it.
If it is a client app that connects directly to sql. The user will still have the permission to call whatever his(her) identity has access to, and the app password is there. If you are not comfortable with the user having that level of access, you need a business tier.
If you still want to proceed with the approach, turn off connection pooling. To reduce the open/close overhead, open connections explicitly.
Full explanation/references:
One issue is it doesn't plays well with connection pooling. That is regardless of linq2sql. See at the end of this in msdn.
There are 2 alternatives since sql server 2005 (msdn link), one is also mentioned in the thread you linked to, but it also points out it can go wrong.
Note that its an unsecured option in a 2 tier scenario, like when the application used by the clients connects directly to sql. In those cases the pwd for any application in the client's computer would be exposed in those. Its more secure if it is a business tier the ones that connects, but that's precisely the case where you really want connection pooling.
The another alternative mentioned in my second link to msdn, works well with connection pooling. It is based on stored procedures, and the execute as statement. The execute as is called inside the procedure, and after the procedure is executed the context is reverted. That is great but really would be giving away a lot from what you get with linq2sql by going the sp route.