sp_changegroup

Changes a user’s group.

Syntax

sp_changegroup grpname, username

Parameters

Examples

Usage

There are additional considerations when using sp_changegroup:
  • Executing sp_changegroup adds the specified user to the specified group. The user is dropped from the group he or she previously belonged to and is added to the one specified by grpname.

  • New database users can be added to groups at the same time they are given access to the database with sp_adduser.

  • Groups are used as a collective name for granting and revoking privileges. Every user is always a member of the default group, “public”, and can belong to only one other group.

  • To remove someone from a group without making that user a member of a new group, use sp_changegroup to change the user’s group to “public”, as shown above in Example 2.

  • When a user changes from one group to another, the user loses all permissions that he or she had as a result of belonging to the old group and gains the permissions granted to the new group.

See also grant, revoke in Reference Manual: Commands.

Permissions

The permission checks for sp_changegroup differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be a user with manage any user privilege.

Disabled

With granular permissions disabled, you must be the database owner, a user with sa_role, or a user with sso_role.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

Related reference
sp_addgroup
sp_adduser
sp_dropgroup
sp_helpgroup