The best and most secure approach to your problem, specially if you're in the face of a "simple" database (and app, I assume), is to use domain accounts for your users and configure the database with the permissions you see fit. You can create windows groups to assign permissions to categories of users (Employee, Manager, Admin, etc). MSDE already installs by default with Windows Integrated authentication enabled, which means that if you use a connection string like the following:
"Data Source=.;Database=MyDb;Integrated Security=true;"
Your users' Windows identity will be used to access the database. That is, you have single sign-on: they log into a domain, and that's it. Remember you need to give them permissions on the database and tables involved. MSDE doesn't come with any administrative interface to do this, and you can only access administrative features through the osql command line utility, which is highly annoying. I suggest you install SQL Server client tools (including Enterprise Manager) and use it to configure users permisions.
In order for the integrated Windows security to work on ASP.NET you would have to enable Windows Authentication in IIS AND the web.config:
<authentication mode="Windows" />
<identity impersonate="true" />
The only restriction is that the database must be running on the same server (ASP.NET-only restriction), which I believe will be the case for any small to medium app.
This was first published in January 2004