sp_addrolemember (T-SQL)

Adds a security account as a member of an existing Microsoft® SQL Server™ database role in the current database.

Syntax

sp_addrolemember [@rolename =] 'role',
    [@membername =] 'security_account'

Arguments

[@rolename =] 'role'

Is the name of the SQL Server role in the current database. role is sysname, with no default.

[@membername =] 'security_account'

Is the security account being added to the role. security_account is sysname, with no default. security_account can be any valid SQL Server user, SQL Server role, or any Microsoft Windows NT® user or group granted access to the current database. When adding Windows NT users or groups, specify the name the Windows NT user or group is known by in the database (added using sp_grantdbaccess).

Return Code Values

0 (success) or 1 (failure)

Remarks

When using sp_addrolemember to add a security account to a role, any permissions applied to the role are inherited by the new member.

When adding a SQL Server role as a member of another SQL Server role, you cannot create circular roles. For example, MyRole cannot be added as a member of YourRole if YourRole is already a member of MyRole. Additionally, you cannot add a fixed database or fixed server role, or dbo to other roles. For example, the db_owner fixed database role cannot be added as a member of the user-defined role YourRole.

Only use sp_addrolemember to add a member to a SQL Server role. Use sp_addsrvrolemember to add a member to a fixed server role. Adding a member to a Windows NT group in SQL Server is not possible.

sp_addrolemember cannot be used inside a user-defined transaction.

Permissions

Only members of the db_securityadmin or db_owner fixed database role can execute sp_addrolemember to add a member to fixed database roles. Role owners can execute sp_addrolemember to add a member to any SQL Server role they own.

Examples

A. Add a Windows NT user

This example adds the Windows NT user Corporate\JeffL to the Sales database as user Jeff. Jeff is then added to the Sales_Managers role in the Sales database.

Note Because Corporate\JeffL is known as the user Jeff in the Sales database, the user name Jeff must be specified using sp_addrolemember.

USE Sales

GO

EXEC sp_grantdbaccess 'Corporate\JeffL', 'Jeff'

GO

EXEC sp_addrolemember 'Sales_Managers', 'Jeff'

B. Add a SQL Server user

This example adds the SQL Server user Michael to the Engineering role in the current database.

EXEC sp_addrolemember 'Engineering', 'Michael'

See Also