sp_addapprole (Transact-SQL)
Applies to:
SQL Server
Adds an application role to the current database.
Important
This feature will be removed in a future version of 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.
Transact-SQL syntax conventions
Syntax
sp_addapprole
[ @rolename = ] N'rolename'
, [ @password = ] N'password'
[ ; ]
Arguments
[ @rolename = ] N'rolename'
The name of the new application role. @rolename is sysname, with no default. @rolename must be a valid identifier and can't already exist in the current database.
Application role names can contain from 1 up to 128 characters, including letters, symbols, and numbers. Role names can't contain a backslash (\
) nor be NULL or an empty string ('').
[ @password = ] N'password'
The password required to activate the application role. @password is sysname, with no default. @password can't be NULL.
Return code values
0
(success) or 1
(failure).
Remarks
In earlier versions of SQL Server, users (and roles) aren't fully distinct from schemas. Beginning with SQL Server 2005 (9.x), schemas are fully distinct from roles. This 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
does the following checks:
If a schema with the same name as the application role doesn't already exist, the schema is created. The new schema is owned by the application role, and it's the default schema of the application role.
If a schema of the same name as the application role already exists, the procedure fails.
sp_addapprole
doesn't check password complexity. Password complexity is checked byCREATE APPLICATION ROLE
.
The parameter @password is stored as a one-way hash.
The sp_addapprole
stored procedure can't be executed from within a user-defined transaction.
Important
The Microsoft ODBC encrypt
option isn't 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.
Permissions
Requires ALTER ANY APPLICATION ROLE permission on the database. If a schema with the same name and owner as the new role doesn't already exist, also requires CREATE SCHEMA permission on the database.
Examples
The following example adds the new application role SalesApp
with the password x97898jLJfcooFUYLKm387gf3
to the current database.
EXEC sp_addapprole 'SalesApp', 'x97898jLJfcooFUYLKm387gf3';
GO