Adds a database user, database role, Windows login, or Windows group to a database role in the current database.
- [ @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.
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
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'