A System Administrator or the Database Owner can use sp_changegroup to change a user’s group affiliation. Each user can be a member of only one group other than “public,” of which all users are always members.
Before you execute sp_changegroup:
The group must exist. (Use sp_addgroup to create a group.)
The user must have access to the current database (must be listed in sysusers).
The syntax for sp_changegroup is:
sp_changegroup grpname, username
For example, to change the user “jim” from his current group to the group “manage,” use:
sp_changegroup manage, jim
To remove a user from a group without assigning the user to another group, you must change the group affiliation to “public”:
sp_changegroup "public", jim
The name “public” must be in quotes because it is a reserved word. This command reduces Jim’s group affiliation to “public” only.
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, but gains the permissions that have been granted to the new group.
The assignment of users into groups can be changed at any time.
