sp_addrole (T-SQL)

Creates a new Microsoft® SQL Server™ role in the current database.

Syntax

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

Arguments

[@rolename =] 'role'

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

[@ownername =] 'owner'

Is the owner of the new role. owner is sysname, with a default of dbo. owner must be a user or role in the current database. When specifying Microsoft Windows NT® users, specify the name the Windows NT user is known by in the database (added using sp_grantdbaccess).

Return Code Values

0 (success) or 1 (failure)

Remarks

SQL Server roles can contain from 1 to 128 characters, including letters, symbols, and numbers. However, roles cannot:

• Contain a backslash character (\).
• Be NULL, or an empty string (‘‘).

After adding a role, use sp_addrolemember to add security accounts as members of the role. When using the GRANT, DENY, or REVOKE statements to apply permissions to the role, members of the role inherit the permissions as if the permissions were applied directly to their accounts.

Note It is not possible to create new fixed server roles. Roles can only be created at the database level.

sp_addrole cannot be used inside a user-defined transaction.

Permissions

Only members of the sysadmin fixed server role or db_securityadmin and db_owner fixed database roles can execute sp_addrole.

Examples

This example adds the new role called Managers to the current database.

EXEC sp_addrole 'Managers'

See Also