ALTER SERVER ROLE

ALTER SERVER ROLE#

Adds a login to a server role, removes one from it, or renames a role.

Syntax#

ALTER SERVER ROLE role name ADD MEMBER login DROP MEMBER login WITH NAME = new role name

Arguments#

role_name

The name of the server role to change (at most 128 characters). Both built-in roles and roles created in the web interface are addressable.

ADD MEMBER login

Makes the login a member of the role. The login must already exist.

DROP MEMBER login

Removes the login from the role.

WITH NAME = new_role_name

Renames the role. Only a role created in the web interface can be renamed; the name must not already be taken by another role or by a login.

Remarks#

The statement is instance-wide and can be executed from any database.

Adding a login that is already a member, removing a login that is not a member, and renaming a role to the name it already has all succeed without changing anything, matching SQL Server. All three are still recorded in the audit trail, which records the attempt rather than its effect.

Querona accepts membership changes on the built-in roles (sysadmin, securityadmin, dbcreator, datareader, viewer), exactly as SQL Server accepts them on its fixed server roles. Renaming a built-in role is refused.

Membership can also be managed in the web interface, under ADMINISTER ‣ User management and ADMINISTER ‣ Role management; see Users and roles.

Differences from SQL Server#

  • The public role cannot be changed. Every user is a member of public implicitly, so ADD MEMBER and DROP MEMBER on it are refused (error 15081). SQL Server refuses the same statement.

  • A role cannot be a member of another role. SQL Server allows a server role as server_principal; Querona models role membership as logins only, and refuses a role name with an explicit error rather than claiming it does not exist.

  • Internal accounts are protected. The system account and the Spark reverse account are refused as members (error 15405), the way SQL Server refuses sa. The admin account is an ordinary login and can be added to and removed from roles.

  • One permission covers everything. SQL Server splits this between fixed-role membership, ALTER ANY SERVER ROLE, CONTROL SERVER and per-role ALTER. Querona gates the whole statement on the single Alter any role permission, so a member of securityadmin can also change the membership of built-in roles.

  • Roles are created in the web interface. There is no CREATE SERVER ROLE statement.

Permissions#

Requires the Alter any role permission, which the securityadmin built-in role carries.

A caller without it receives the same error as one naming a role that does not exist, so the statement cannot be used to discover which roles exist.

Errors#

Number

Condition

15023

The new role name is already taken by a role or by a login.

15081

Membership of the public role cannot be changed.

15150

The role is built in and cannot be renamed.

15151

The role does not exist, the login does not exist, or the caller lacks permission.

15405

The login is an internal Querona account and cannot be a member of a role.

Examples#

Add a login to a role, and remove it again.

ALTER SERVER ROLE analysts ADD MEMBER jkowalski;
ALTER SERVER ROLE analysts DROP MEMBER jkowalski;

Give a service login the rights of securityadmin.

ALTER SERVER ROLE securityadmin ADD MEMBER integration_svc;

Rename a role.

ALTER SERVER ROLE analysts WITH NAME = analyst;

Read back who belongs to which role. Logins and roles draw their ids from one pool, so the join needs no type predicate.

SELECT r.name AS role_name, m.name AS member_name
FROM sys.server_role_members srm
JOIN sys.server_principals r ON srm.role_principal_id = r.principal_id
JOIN sys.server_principals m ON srm.member_principal_id = m.principal_id;

See also