How to Authenticate User and Access SQL Application Roles for Stored Procedure Execution

 

I have read a numerous of web sites pertaining to authentication and authorization but it simply is not coming to getther for me-yet.

The goal is to authenticate users then allow acces based on their Application Roles.  Thus far I have created User Groups which does work.
Now I need to be able to access the Application Role for execution of the various stored procedures; somehow the execution of the stored procedures is occuring within authorization via the
Application Roles.

This is what I have thus far:

  <connectionStrings>
  <add name="TiresConnectionString" connectionString="Data Source=Domain\Instance;Initial Catalog=Tires;Integrated Security=SSPI"/>
</connectionStrings>

SQL 2005
Users
  AllUsers
  Supervisors
  Admin

Roles
Application Roles
  TireCRUD
  T ...