Changes a user’s group.
sp_changegroup grpname, username
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.
is the name of the user to be added to the group. The user must already exist in the current database.
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
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
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.
The permission checks for sp_changegroup differ based on your granular permissions settings.
Granular permissions enabled |
With granular permissions enabled, you must be a user with manage any user privilege. |
Granular permissions 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:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
System procedures sp_addgroup, sp_adduser, sp_dropgroup, sp_helpgroup
