sp_addrole
Applies to: SQL Server
Adds a user-defined database role to the current database.
Syntax
EXEC sp_addrole
[@rolename =] 'role'`
[, [@ownername =] 'owner']
Arguments
| Parameter | Description |
|---|---|
@rolename |
The name of the new role to be created. Role names must be unique within the database and follow the rules for identifiers. @rolename is sysname, with no default. |
@ownername |
The owner of the new role. If not specified, the role defaults to the current user. If @ownername is specified as 'sa' or 'dbo', the role is owned by the database owner. If @ownername is a fixed server role, the role is owned by the fixed server role. @ownername is sysname, with a default of NULL. |
Return Value
0 (success) or 1 (failure).
Permissions
Requires membership in the db_owner fixed database role or membership in the db_securityadmin fixed database role.
Examples
A. Creating a new role
The following example creates a new database role named Sales.
EXEC sp_addrole 'Sales';
B. Creating a new role owned by the database owner
The following example creates a new database role named Marketing and assigns ownership to the database owner.
EXEC sp_addrole 'Marketing', 'dbo';
Remarks
Note: You cannot create a role that already exists in the current database. You must first drop the existing role before you can create a new role with the same name.
sp_addrole is a stored procedure that belongs to the master database. It is executed in the context of the current database.
Database roles are used to manage permissions within a database. You can grant permissions to roles, and then add users to those roles to grant them the same permissions.
Tip: Consider using role templates and inheritance to simplify role management.