New Information - SQL Server 2000 SP3.
Adds a special type of role in the current database used for application security.
sp_addapprole [ @rolename = ] 'role'
, [ @password = ] 'password'
[ @rolename = ] 'role'
Is the name of the new role. role is sysname, with no default. role must be a valid identifier and cannot already exist in the current database.
[ @password = ] 'password'
Is the password required to activate the role. password is sysname, with no default. password is stored in encrypted form.
Return Code Values
0 (success) or 1 (failure)
Microsoft® SQL Server™ roles can contain from 1 through 128 characters, including letters, symbols, and numbers. However, roles cannot:
- Contain a backslash (\).
- Be NULL or an empty string.
The fundamental differences between standard and application roles are:
- Application roles contain no members. Users, Microsoft Windows NT® groups, and roles cannot be added to application roles; the permissions of the application role are gained when the application role is activated for the user's connection through a specific application(s). A user's association with an application role results from being able to run an application that activates the role, rather than being a member of the role.
- Application roles are inactive by default. They are activated by using sp_setapprole and require a password.
Security Note When possible, prompt users to enter their credentials at run time. Avoid storing credentials in a file. If you must persist credentials, you should encrypt them with the Win32 cryptoAPI.
- When an application role is activated for a connection by the application, the connection permanently loses all permissions applied to the login, user account, or other groups or database roles in all databases for the duration of the connection. The connection gains the permissions associated with the application role for the database in which the application role exists. Because application roles are applicable only to the database in which they exist, the connection can gain access to another database only through permissions granted to the guest user account in the other database. Therefore, if the guest user account does not exist in a database, the connection cannot gain access to that database. If the guest user account does exist in the database but permissions to access an object are not explicitly granted to guest, the connection cannot access that object, regardless of who created the object. The permissions the user gained from the application role remain in effect until the connection logs off from SQL Server.
sp_addapprole cannot be executed from within a user-defined transaction.
Only members of the sysadmin fixed server role, and the db_owner and db_securityadmin fixed database roles can execute sp_addapprole.
This example adds the new application role SalesApp to the current database with the password xyz_123.
EXEC sp_addapprole 'SalesApp', 'xyz_123'