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.

System_CAPS_importantImportant

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.

System_CAPS_noteNote

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;
GO
CREATE USER Mary5 FOR LOGIN [Contoso\Mary5] ;
GO

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

ADD
Show:
© 2015 Microsoft