Adds a database user, database role, Windows login, or Windows group to a database role in the current database.
sp_addrolemember [ @rolename = ] 'role', [ @membername = ] 'security_account'
- [ @rolename = ] 'role'
Is the name of the database role in the current database. role is a sysname, with no default.
- [ @membername = ] 'security_account'
Is the security account being added to the role. security_account is a sysname, with no default. security_account can be a database user, database role, Windows login, or Windows group.
Return Code Values
0 (success) or 1 (failure)
A member added to a role by using sp_addrolemember inherits the permissions of the role. If the new member is a Windows-level principal without a corresponding database user, a database user will be created.
A role cannot include itself as a member. Such "circular" definitions are not valid, even when membership is only indirectly implied by one or more intermediate memberships.
sp_addrolemember cannot add a fixed database role, fixed server role, or dbo to a role. Prior to SQL Server Serivce Pack 1, sp_addrolemember cannot be executed in a user-defined transaction.
Only use sp_addrolemember to add a member to a database role. To add a member to a server role, use sp_addsrvrolemember (Transact-SQL).
Do not add flexible roles as members of fixed roles. This could enable unintended privilege escalation.
Adding members to flexible database roles 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.
Adding members to fixed database roles requires membership in the db_owner fixed database role.
A. Adding a Windows login
The following example adds the Windows login
AdventureWorks database as user
Mary5. The user
Mary5 is then added to the
Contoso\Mary5 is known as the database user
Mary5 in the AdventureWorks database, the user name
Mary5 must be specified by using
USE AdventureWorks GO EXEC sp_grantdbaccess 'Contoso\Mary5', 'Mary5' GO EXEC sp_addrolemember 'Production', 'Mary5'
B. Adding a database user
The following example adds the database user
Mary5 to the
Production database role in the current database.
EXEC sp_addrolemember 'Production', 'Mary5'