Skip to content

allow-non-administrators-to-use-replication-monitor.md

Latest commit

 

History

History
81 lines (49 loc) · 4.79 KB

allow-non-administrators-to-use-replication-monitor.md

File metadata and controls

81 lines (49 loc) · 4.79 KB
title Allow Non-Administrators use Replication Monitor
description Learn how to grant access to non-administrators to the Replication Monitor in SQL Server Management Studio (SSMS).
author MashaMSFT
ms.author mathoma
ms.date 09/25/2024
ms.service sql
ms.subservice replication
ms.topic how-to
ms.custom
updatefrequency5
helpviewer_keywords
Replication Monitor, non-administrators access
monikerRange =azuresqldb-mi-current||>=sql-server-2016

Allow Non-Administrators to Use Replication Monitor

[!INCLUDEsql-asdbmi] This topic describes how to allow non-administrators to use Replication Monitor in [!INCLUDEssnoversion] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql]. Replication Monitor can be used by users who are members of the following roles:

  • The sysadmin fixed server role.

    These users can monitor replication and have full control over changing replication properties such as agent schedules, agent profiles, and so on.

  • The replmonitor database role in the distribution database.

    These users can monitor replication, but cannot change any replication properties.

In This Topic

Before You Begin

Security

Permissions

To allow non-administrators to use Replication Monitor, a member of the sysadmin fixed server role must add the user to the distribution database and assign that user to the replmonitor role.

Using SQL Server Management Studio

To allow non-administrators to use Replication Monitor

  1. In [!INCLUDEssManStudioFull], connect to the Distributor, and then expand the server node.

  2. Expand Databases, expand System Databases, and then expand the distribution database (named distribution by default).

  3. Expand Security, right-click Users, and then click New User.

  4. Enter a user name and login for the user.

  5. Select a default schema of replmonitor.

  6. Select the replmonitor check box in the Database role membership grid.

  7. Select OK.

Using Transact-SQL

To add a user to the replmonitor fixed database role

  1. At the Distributor on the distribution database, execute sp_helpuser (Transact-SQL). If the user is not listed in UserName in the result set, the user must be granted access to the distribution database using the CREATE USER (Transact-SQL) statement.

  2. At the Distributor on the distribution database, execute sp_helprolemember (Transact-SQL), specifying a value of replmonitor for the @rolename parameter. If the user is listed in MemberName in the result set, the user already belongs to this role.

  3. If the user does not belong to the replmonitor role, execute sp_addrolemember (Transact-SQL) at the Distributor on the distribution database. Specify a value of replmonitor for @rolename and the name of the database user or the [!INCLUDEmsCoName] Windows login being added for @membername.

To remove a user from the replmonitor fixed database role

  1. To verify that the user belongs to the replmonitor role, execute sp_helprolemember (Transact-SQL) at the Distributor on the distribution database, and specify a value of replmonitor for @rolename. If the user is not listed in MemberName in the result set, the user does not currently belong to this role.

  2. If the user does belong to the replmonitor role, execute sp_droprolemember (Transact-SQL) at the Distributor on the distribution database. Specify a value of replmonitor for @rolename and the name of the database user or the Windows login being removed for @membername.