I almost agree with Bob (I'm not a fan of application roles).

Most of the applications that I have worked on have implemented user security within the application through a user table which stores username and passwords. The connection to the SQL Server is made via a single login. This provides the best use of connection pooling and is most appropriate for web applications.

If you're writing a client/server application with a rich client that is doing all of the data access (your typical two-tier app), you might want to look into Windows Integrated security.

-Mike