asp.net membership and sql server objects -- help needed

[Ranginald]

I've been doing a lot of reading but I am still confused about asp.net
membership and how it relates to sql server users and objects.


Normally with sql server in a network setting it's easy to set
permission level access to tables and views. Done.

But now assume I have an asp.net web app, where the data is
database1.mdf and the user info is in aspnetdb.mdf. Is there a way to
restrict access to certain sql server objects via the asp.net
membership?

For example, I'd like to allow members of the role "workers" to have
access to storedProcedureWorker1, and storedProcedureWorker2,

and for members of the role "managers" to have access to spManagers1
and spManagers2.

Also, what asp.net user is it that is accessing the sql server
database from the asp.net application? I have a connection string
where I give a username and password to access the sqlserver database
-- in the past I would assign the sqlserver user whatever permissions
it needs for whatever objects I want the asp.net app to get to, but
now that I'm moving to a larger application with multiple users, I
don't understand the archietecture.

Thanks so much for any help in advance!

Ranginald