Skip to content

Latest commit

 

History

History
80 lines (55 loc) · 3.2 KB

sp-addrole-transact-sql.md

File metadata and controls

80 lines (55 loc) · 3.2 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sp_addrole (Transact-SQL)
sp_addrole creates a new database role in the current database.
VanMSFT
vanto
randolphwest
08/22/2024
sql
system-objects
reference
sp_addrole
sp_addrole_TSQL
sp_addrole
TSQL

sp_addrole (Transact-SQL)

[!INCLUDE SQL Server]

Creates a new database role in the current database.

Important

sp_addrole is included for compatibility with earlier versions of [!INCLUDE ssNoVersion] and might not be supported in a future release. Use CREATE ROLE instead.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sp_addrole
    [ @rolename = ] N'rolename'
    [ , [ @ownername = ] N'ownername' ]
[ ; ]

Arguments

[ @rolename = ] N'rolename'

The name of the new database role. @rolename is sysname, with no default. @rolename must be a valid identifier and must not already exist in the current database.

[ @ownername = ] N'ownername'

The owner of the new database role. @ownername is sysname, with a default of the current executing user. @ownername must be a database user or database role in the current database.

Return code values

0 (success) or 1 (failure).

Remarks

The names of [!INCLUDE ssNoVersion] database roles can contain from 1 through 128 characters, including letters, symbols, and numbers. The names of database roles can't contain a backslash character (\), be NULL, or an empty string ('').

After you add a database role, use sp_addrolemember to add principals to the role. When GRANT, DENY, or REVOKE statements are used to apply permissions to the database role, members of the database role inherit those permissions as if the permissions were applied directly to their accounts.

Note

New server roles can't be created. Roles can only be created at the database level.

sp_addrole can't be used inside a user-defined transaction.

Permissions

Requires CREATE ROLE permission on the database. If creating a schema, requires CREATE SCHEMA on the database. If @ownername is specified as a user or group, requires IMPERSONATE on that user or group. If @ownername is specified as a role, requires ALTER permission on that role or on a member of that role. If owner is specified as an application role, requires ALTER permission on that application role.

Examples

The following example adds a new role called Managers to the current database.

EXEC sp_addrole 'Managers';

Related content