Skip to main content

ALTER ROLE (bSQL) | Blockpoint Docs

Adds or removes members to or from a database role, or changes the name of a user-defined database role. For examples, see Examples.

bSQL Syntax Conventions

Syntax

ALTER ROLE  role_name  
{
ADD MEMBER database_principal
| DROP MEMBER database_principal
| WITH NAME = new_name
}

Arguments

role_name
Specifies the database role to change.

ADD MEMBER database_principal

Specifies to add the database principal to the membership of a database role.

  • database_principal is a database user or a user-defined database role.
  • database_principal cannot be a fixed database role.

DROP MEMBER database_principal

Specifies to remove a database principal from the membership of a database role.

  • database_principal is a database user or a user-defined database role.
  • database_principal cannot be a fixed database role.

WITH NAME = new_name

Specifies to change the name of a user-defined database role. The new name must not already exist in the database.

Changing the name of a database role does not change ID number, owner, or permissions of the role.

Permissions

To run this command you need one or more of these permissions or memberships:

  • Ownership of the role and ALTER permission on the database.
  • Membership in the admin fixed database role.

Limitations and restrictions

You cannot change the name of a fixed database role.

Examples

A. Change the name of a database role.

The following example changes the name of role writer to financial_specialist. This example can be executed in the DEMO DATABASE.

ALTER ROLE writer WITH NAME = financial_specialist

B. Add or remove role members.

This example creates a database role named authorizer. It adds a database user named Barry to the membership, and then shows how to remove the member Barry. This example can be executed in the DEMO DATABASE.

CREATE ROLE authorizer
ALTER ROLE authorizer ADD MEMBER barry
ALTER ROLE authorizer DROP MEMBER barry

See Also