Skip to content

sp-addrolemember-transact-sql.md

Latest commit

 

History

History
138 lines (92 loc) · 5.39 KB

sp-addrolemember-transact-sql.md

File metadata and controls

138 lines (92 loc) · 5.39 KB
title sp_addrolemember (Transact-SQL)
description Adds a database user, database role, Windows login, or Windows group to a database role in the current database.
author VanMSFT
ms.author vanto
ms.reviewer randolphwest
ms.date 06/23/2025
ms.service sql
ms.subservice system-objects
ms.topic reference
ms.custom
ignite-2025
f1_keywords
sp_addrolemember_TSQL
sp_addrolemember
helpviewer_keywords
sp_addrolemember
dev_langs
TSQL
monikerRange >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb

sp_addrolemember (Transact-SQL)

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

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.

EXECUTE sp_addrolemember 'Production', 'Mary5';

Examples: Analytics Platform System (PDW)

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

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

EXECUTE sp_addrolemember 'Production', 'UserMary';

Related content