Share via


How to: Allow Non-Administrators to Use Replication Monitor (Replication Transact-SQL Programming)

The ability to monitor a replication topology is restricted to the sysadmin fixed server role, the db_owner fixed database role, and the replmonitor fixed database role on the distribution database. You can add users to the replmonitor fixed database role programmatically using system stored procedures.

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