sp_changegroup
Changes the role membership for the security account of a user in the current database. This procedure is provided for backward compatibility. Microsoft® SQL Server™ version 7.0 uses roles instead of groups. Use sp_addrolemember instead.
Syntax
sp_changegroup [ @grpname = ] 'role'
, [ @username = ] 'user'
Arguments
[@grpname =] 'role'
Is the role to which the user is added. role is sysname, with no default. role must exist in the current database.
[@username =] 'user'
Is the user to add to the role. user is sysname, with no default. The user must already exist in the current database. When specifying Windows NT users, specify the name the Windows NT user is known by in the database (added using sp_grantdbaccess).
Return Code Values
0 (success) or 1 (failure)
Remarks
Roles provide a mechanism for managing the permissions applied to the members of the role. When adding a user to a role, the user gains the permissions defined for the role.
When sp_changegroup is executed, the security account for user is added as a member of role, and removed from all other roles. sp_addrolemember and sp_droprolemember can be used to change role membership in a single role without affecting membership in other roles.
New database users can be added to roles at the same time they are given access to the database with sp_adduser.
Every user is a member of the default role public, if not explicitly added to some other role by sp_addrolemember.
sp_changegroup cannot be executed within a user-defined transaction.
Permissions
Members of the sysadmin fixed server role, and the db_owner and db_securityadmin fixed database roles can execute sp_changegroup for any role in the database.
Role owners can execute sp_changegroup. The role owner must own both the new role and the current role of the user.
Examples
This example makes the user Albert a member of the developers role.
EXEC sp_changegroup 'developers', 'Albert'