Below is a common dilemma is our User/Database/Application relationships:

We want all the users to access the Database (SQL Server) via Applications, but we don’t want Users to use their accounts to access the Database directly. If we use conventional way of assigning Users (Domain Users or SQL server users) certain Database rights, then this user may use these rights to run application to access the database, or to access the database directly. There is virtually no way to distinguish between them.

The SQL Server Application Role can solve this problem by the following scenario:

Step 1: We assign users minimum rights to login to the server, and the minimum right to use the database. So no user can really do any damage to the database if he/she does login directly.

Step 2: Create an Application Role, say myAppRole for that specific database, say database1, and assign just enough right for that myAppRole.

Step 3: When this Web application App1 connects to the database, the App1 program will execute a stored procedure called sp_setapprole, to activate the role myAppRole, with the proper (encrypted) password.

Step 4: Once the application role is activated, the application will forget all the previous rights from the logged in user, and adopt the new rights defined by the myAppRole.

This approach effectively achieve the goal of assigning the database rights to the Application, but not to the Users. The Users have to run certain application to get certain right. Without running applications, the User can’t do any damage to the SQL Server and database.

For detailed information, please read:

http://builder.com.com/5100-6388-5068954-3.html