Export (0) Print
Expand All
Expand Minimize
This topic has not yet been rated - Rate this topic

sp_addrole (Transact-SQL)

Creates a new database role in the current database.

Important noteImportant

sp_addrole is included for compatibility with earlier versions of MicrosoftSQL Server and may not be supported in a future release. Use CREATE ROLE instead.

Topic link iconTransact-SQL Syntax Conventions

sp_addrole [ @rolename = ] 'role' [ , [ @ownername = ] 'owner' ] 
[ @rolename = ] 'role'

Is the name of the new database role. role is a sysname, with no default. role must be a valid identifier (ID) and must not already exist in the current database.

[ @ownername =] 'owner'

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

0 (success) or 1 (failure)

The names of SQL Server database roles can contain from 1 through 128 characters, including letters, symbols, and numbers. The names of database roles cannot :contain a backslash character (\), be NULL, or an empty string ('').

After you add a database role, use sp_addrolemember (Transact-SQL) 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.

NoteNote

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

sp_addrole cannot be used inside a user-defined transaction.

Requires CREATE ROLE permission on the database. If creating a schema, requires CREATE SCHEMA on the database. If owner is specified as a user or group, requires IMPERSONATE on that user or group. If owner 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.

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

EXEC sp_addrole 'Managers'
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.