Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

sp_addrolemember (Transact-SQL)


Applies To: SQL Server 2014, SQL Server 2016 Preview

Adds a database user, database role, Windows login, or Windows group to a database role in the current database.


This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER ROLE instead.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

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.

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 but may not be fully mapped to the login. Always check that the login exists and has access to the database.

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. sp_addrolemember cannot be executed within 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).

Adding members to flexible database roles requires one of the following:

  • Membership in the db_securityadmin or db_owner fixed database role.

  • Membership in the role that owns the role.

  • ALTER ANY ROLE permission or ALTER permission on the role.

Adding members to fixed database roles requires membership in the db_owner fixed database role.

The following example adds the Windows login Contoso\Mary5 to the AdventureWorks2012 database as user Mary5. The user Mary5 is then added to the Production role.


Because Contoso\Mary5 is known as the database user Mary5 in the AdventureWorks2012 database, the user name Mary5 must be specified. The statement will fail unless a Contoso\Mary5 login exists. Test by using a login from your domain.

USE AdventureWorks2012;
CREATE USER Mary5 FOR LOGIN [Contoso\Mary5] ;

The following example adds the database user Mary5 to the Production database role in the current database.

EXEC sp_addrolemember 'Production', 'Mary5';
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft