Adds an application role to the current database.
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CREATE APPLICATION ROLE instead.
In earlier versions of SQL Server, users (and roles) are not fully distinct from schemas. Beginning with SQL Server 2005, schemas are fully distinct from roles. This new architecture is reflected in the behavior of CREATE APPLICATION ROLE. This statement supersedes sp_addapprole.
To maintain backward compatibility with earlier versions of SQL Server, sp_addapprole will do the following:
If a schema with the same name as the application role does not already exist, such a schema will be created. The new schema will be owned by the application role, and it will be the default schema of the application role.
If a schema of the same name as the application role already exists, the procedure will fail.
Password complexity is not checked by sp_addapprole. But password complexity is checked by CREATE APPLICATION ROLE.
The parameter password is stored as a one-way hash.
The sp_addapprole stored procedure cannot be executed from within a user-defined transaction.
The Microsoft ODBC encrypt option is not supported by SqlClient. When you can, prompt users to enter application role credentials at run time. Avoid storing credentials in a file. If you must persist credentials, encrypt them by using the CryptoAPI functions.
The following example adds the new application role SalesApp with the password x97898jLJfcooFUYLKm387gf3 to the current database.
EXEC sp_addapprole 'SalesApp', 'x97898jLJfcooFUYLKm387gf3' ; GO