SQL Server Troubleshooting
Distribution Agent: Only members of the sysadmin or db_owner roles can
perform this operation.
A distribution agent has failed, the full error message text is:
Only members of the sysadmin or db_owner roles can perform
the last command may be recorded as:
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
- First identify the subscriber where the problem lies. Look at the
agent error details, the subscriber name will be the first part of the
- Next identify which account is being used for the agent. Do this
Run server enterprise manager
Select the distribution server
Go to the 'Tools' menu, select 'Replication' and from the drop
down menu select 'Configure Publishing, Subscribers, and Distribution...'.
Select the 'Subscribers' tab.
Double-click the name of the subscriber to display its properties.
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.
- 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
- 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.
- 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
- 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.
These notes are believed to be correct for SQL
Server 7 and may apply to other versions as well.
About the author: Brian Cryer
is a dedicated software developer and webmaster. For his day job he
develops websites and desktop applications as well as providing
IT services. He moonlights as a technical author and consultant.