Skip to content

Latest commit

 

History

History
89 lines (59 loc) · 3.57 KB

sp-addapprole-transact-sql.md

File metadata and controls

89 lines (59 loc) · 3.57 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sp_addapprole (Transact-SQL)
Adds an application role to the current database.
VanMSFT
vanto
randolphwest
08/21/2024
sql
system-objects
reference
sp_addapprole_TSQL
sp_addapprole
sp_addapprole
TSQL

sp_addapprole (Transact-SQL)

[!INCLUDE SQL Server]

Adds an application role to the current database.

Important

[!INCLUDE ssNoteDepFutureAvoid] Use CREATE APPLICATION ROLE instead.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: 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 [!INCLUDE ssNoVersion], users (and roles) aren't fully distinct from schemas. Beginning with [!INCLUDE ssVersion2005], 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 [!INCLUDE ssNoVersion], 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 by CREATE 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

Related content