Creating Application Roles in SQL Server2000/2005

[maurices5000]

Ok, i created a database in SQL Server. I also have an app i created in VB6 that both selects data from the database and writes to the database.

I have a pretty basic understanding of how to add users to a database. But my book says little about application roles. Can i assign the application a ROLE and then that app can access the database as needed? Or do i have to assign users a group and grant that group access to the database as well as grant the app a role?

For example, can i just install the app on everyone's computers. Give the app a role in the db and not provide the users with a SQL Server login or db login?

From what i read, i do think i will need to do both because although users gain access through the application role everything is monitored based on their logins. But if i don't grant the users permission to access the database, then how will SQL Server know who is using the app role.

Here is my general understanding
Using Windows Authentication is best. I can grant access to Windows Groups or individual logins to SQL Server but that does not grant them access to any particular database. I can use exec sp_grantdbaccess to add a Windows Group to the database. Yet i wonder if somehow they will be able to view the information in the database which is what I DON"T WANT OUTSIDE OF THE APPLICATION. I can create an application role and give it access taht i want.

What prevents the users from viewing the SQL database without the application?

To turn the application role on do i have to add "exec sp_setapprole 'EmpTest', {Encrypt N 'password'}, 'odbc'" to my application source code and create a new executable?

When should i turn the role off or should i always leave it on?

Thanks guys!

[rmiao]

With application role, it's not assign app to the role. What you need to do is create application role in sql db, and activate it in you app. The purpose of it is restrict user access from app only. You can find details in sql books online.