sp_changegroup

Syntax

sp_changegroup grpname, username

grpname – is the name of the group. The group must already exist in the current database. If you use “public” as the grpname, enclose it in quotes, because it is a keyword.
username – is the name of the user to be added to the group. The user must already exist in the current database.

Example 1 – The user “albert” is now a member of the “fort_mudge” group. It doesn’t matter what group “albert” belonged to before:
```
sp_changegroup fort_mudge, albert
```
Example 2 – Removes “albert” from the group he belonged to without making him a member of a new group (all users are always members of “public”):
```
sp_changegroup "public", albert
```

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.

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

Setting	Description
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.

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

Information	Values
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