sp_changegroup (Transact-SQL)

Changes the role membership of a user in the current database.


sp_changegroup is provided for compatibility with earlier versions of Microsoft SQL Server. In new development work, use sp_addrolemember and sp_droprolemember instead.

Topic link iconTransact-SQL Syntax Conventions


sp_changegroup [ @grpname= ] 'role' 
     , [ @username = ] 'user'


  • [ @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.

Return Code Values

0 (success) or 1 (failure)


Roles provide a mechanism for managing the permissions applied to members of the role. When a user is added to a role, the user gains the permissions granted to 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. To change role membership in a single role without affecting membership in other roles, use sp_addrolemember and sp_droprolemember.

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.

sp_changegroup cannot be executed within a user-defined transaction.


Requires one of the following: membership in the db_owner fixed database role; membership in the db_securityadmin fixed database role; membership in the role that owns the role; ALTER permission on the role.


The following example makes the user Albert a member of the developers role.

EXEC sp_changegroup 'developers', 'Albert'

See Also


Security Stored Procedures (Transact-SQL)
sp_addrole (Transact-SQL)
sp_addrolemember (Transact-SQL)
sp_adduser (Transact-SQL)
sp_dropgroup (Transact-SQL)
sp_helpgroup (Transact-SQL)
System Stored Procedures (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance