Home
Trouble shooting
 

Cry about...
SQL Server Troubleshooting

Distribution Agent: Only members of the sysadmin or db_owner roles can perform this operation.

Symptom:

A distribution agent has failed, the full error message text is:

Only members of the sysadmin or db_owner roles can perform this operation.

the last command may be recorded as:

exec sp_MSreplcheck_subscribe

Cause:

The subscriber is causing the error, not the publisher. The subscriber is rejecting the distribution agent login. To the subscriber the distribution agent must be a member of the sysadmin or db_owner role, but it is a member of neither.

Remedy:

  1. First identify the subscriber where the problem lies. Look at the agent error details, the subscriber name will be the first part of the shown 'Subscription'.

  2. Next identify which account is being used for the agent. Do this by:

  1. Run server enterprise manager

  2. Select the distribution server

  3. Go to the 'Tools' menu, select 'Replication' and from the drop down menu select 'Configure Publishing, Subscribers, and Distribution...'.

  4. Select the 'Subscribers' tab.

  5. Double-click the name of the subscriber to display its properties.

  6. On the 'General' tab it will show which account is being used (or attempted to be used) on the subscriber. This will either be the 'SQL Server Agent' on the distribution database (the default) or a specified named account.

  • If the distribution database and the subscriber are in different domains and no trust relationship exists between the domains (specifically the subscriber domain does not trust the distribution domain) then a specified named account must be used.

  1. On the subscriber check that this agent login account is a valid login. On the 'Logins' tab (within Server Enterprise Manager) the account should be listed or a global group that the account is a member of should be listed.

  • Remember: If the agent is using the 'SQL Server Agent' account on the distribution database, and the distribution database server and subscriber are in different domains then the name shown in the subscriber's login list should include the domain specifier.

  1. On the subscriber check that the agent login account has either 'sysadmin' or 'db_owner' roles for the database being replicated. If it is a member of neither then either make it a 'db_owner' of the database or grant it the server role 'System Administrators'. The implications of each being:

  • db_owner: Can create, drop or pull subscriptions.

  • System Administrators: Can set up and configure replication and do everything a db_owner can do.

I would recommend that 'db_owner' is granted because it conveys less privileges to a process that is initiated from another server.

To check that the problem has been resolved, find the distribution agent using 'SQL Server Enterprise Manager', right click it and select 'Start'. You will need to refresh the view or double click the agent to determine whether it has run successfully.