sp_addrole
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, and the 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'