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.
Only the Database Owner, a System Administrator, or a System Security Officer can execute sp_changegroup.
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
