sp_changegroup (T-SQL)

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 the user is added to. 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 db_owner or db_securityadmin fixed database roles can execute sp_changegroup for any role in the database.

Role owners can execute sp_changegroup for the roles they own.

Examples

This example makes the user Albert a member of the developers role.

EXEC sp_changegroup 'developers', 'Albert'

See Also