ALTER ROLE (Transact-SQL)
Modifies a server-level or database-level role.
ALTER ROLE role_name
[ ADD MEMBER security_account [ ,...n ] ]
[ DROP MEMBER security_account [ ,...n ] ]
[ WITH old_password = 'password', new_password = 'password' ]
[ ENABLE | DISABLE ]
[ WITH NAME = new_role_name ]
[ AUTHORIZATION owner_name ]
Arguments
role_name- The name of the role to be altered. Role names must be unique within their scope (server or database).
ADD MEMBER security_account- Specifies one or more security accounts (users or other roles) to add to the role.
DROP MEMBER security_account- Specifies one or more security accounts to remove from the role.
WITH old_password = 'password', new_password = 'password'- Used for changing the password of a role. Not applicable to all role types.
ENABLE | DISABLE- Enables or disables the role. Disabled roles cannot be used to authenticate.
WITH NAME = new_role_name- Renames the role to
new_role_name. AUTHORIZATION owner_name- Assigns a new owner to the role. The
owner_namemust be a valid security principal.
Permissions
To alter a role, the user must have ALTER ANY ROLE permission (for server roles) or membership in the role being altered, or ALTER permission on the role (for database roles).
Examples
Example 1: Adding a member to a database role
USE 'MyDatabase';
GO
ALTER ROLE db_datareader
ADD MEMBER SQLUser01;
GO
Example 2: Removing a member from a server role
ALTER SERVER ROLE sysadmin
DROP MEMBER AdminUser02;
GO
Example 3: Renaming a database role
USE 'MyDatabase';
GO
ALTER ROLE AppRole
WITH NAME = ApplicationUsers;
GO
Note:
Altering roles requires careful consideration of security implications. Always test changes in a development or staging environment before applying them to production.
Tip:
You can use the `sp_helpsrvrolemember` and `sp_helpdbfixedrole` stored procedures to view the members of server and database roles, respectively.