sp_addrolemember
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 that 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 sysadmin fixed server role and the 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. Members of the db_securityadmin fixed database role can add users to any user-defined role.
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 username 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'