Thread: MS SQL Server General Questions/Applicaton Roles

Applicaton Roles
1. Create login:
[B]EXEC sp_addlogin user,'password',MYDATABASE [/B]
2. Granting Access to a specific Database
[B]EXEC sp_grantdbaccess 'MYDATABASE' [/B]
3. Create Application Role
[B]EXEC sp_addapprole MyAppRole, 'password2' [/B]
4. Assign Permissions to the Application Role
5.Inside your program executes a system sproc
[B]EXEC sp_setapprole MyAppRole. {Encrypt N'password2'},'odbc' [/B]
6. Here will be a good idea check inside your application that you have access inside application role...

The process works like this:
1. The user logs in (presumably using a login screen provided by ours application)
2. The login is validated, and the user receives his or her access rights
3. The application executes a system sproc called sp_setapprole and provides a role name and password (we cannot run this SP inside another SP!)
4. The application role is validated, and the connection is switched to the context of that application role (all the rights of the application role).
5. The user continues with access based on the application role rather than his or her personal login throughout the duration of the connection - the user cannot go back to his or her own access information.

to be continue...

Some additional examples  KB906549
SQL Server 2000 Security - Part 5 (Application roles) SQL Server 2000