Skip to content

Latest commit

 

History

History
133 lines (90 loc) · 5.32 KB

sp-addrolemember-transact-sql.md

File metadata and controls

133 lines (90 loc) · 5.32 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
sp_addrolemember (Transact-SQL)
Adds a database user, database role, Windows login, or Windows group to a database role in the current database.
VanMSFT
vanto
randolphwest
08/22/2024
sql
system-objects
reference
sp_addrolemember_TSQL
sp_addrolemember
sp_addrolemember
TSQL
>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current

sp_addrolemember (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw]

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

Important

[!INCLUDE ssNoteDepFutureAvoid] Use ALTER ROLE instead.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sp_addrolemember
    [ @rolename = ] N'rolename'
    , [ @membername = ] N'membername'
[ ; ]

Note

[!INCLUDE synapse-analytics-od-unsupported-syntax]

Arguments

[ @rolename = ] N'rolename'

The name of the database role in the current database. @rolename is sysname, with no default.

[ @membername = ] N'membername'

The security account being added to the role. @membername is sysname, with no default. @membername can be a database user, database role, Windows login, or Windows group.

Return code values

0 (success) or 1 (failure).

Remarks

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

A role can't include itself as a member. Such circular definitions aren't valid, even when membership is indirectly implied by one or more intermediate memberships.

sp_addrolemember can't add a fixed database role, fixed server role, or dbo to a role.

Only use sp_addrolemember to add a member to a database role. To add a member to a server role, use sp_addsrvrolemember.

Permissions

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

  • 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.

Examples

A. Add a Windows login

The following example adds the Windows login Contoso\Mary5 to the [!INCLUDE sssampledbobject-md] 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 [!INCLUDE sssampledbobject-md] database, the user name Mary5 must be specified. The statement fails unless a Contoso\Mary5 login exists. Test by using a login from your domain.

USE AdventureWorks2022;
GO
CREATE USER Mary5 FOR LOGIN [Contoso\Mary5];
GO

B. Add 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';

Examples: [!INCLUDE ssPDW]

C. Add a Windows login

The following example adds the login LoginMary to the [!INCLUDE sssampledbobject-md] database as user UserMary. The user UserMary is then added to the Production role.

Note

Because the login LoginMary is known as the database user UserMary in the [!INCLUDE sssampledbobject-md] database, the user name UserMary must be specified. The statement will fail unless a Mary5 login exists. Logins and users usually have the same name. This example uses different names to differentiate the actions affecting the login vs. the user.

-- Uses AdventureWorks2022
CREATE USER UserMary FOR LOGIN LoginMary;
GO
EXEC sp_addrolemember 'Production', 'UserMary'

D. Add a database user

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

EXEC sp_addrolemember 'Production', 'UserMary'

Related content