SQL Server – Replication – Error – Permission – Snapshot (Proxy 3) – sp_MSadd_snapshot_history


Setting up Replication on my laptop and running into a bunch of permission errors.

In the next few posts, I will touch on some of them.


There are a few SQL Server Agent jobs created.

Here is the Job, we will review in this post.

  • Category :- REPL-Snapshot


Here is the Job Step:

  • Step name :- Snapshot Agent startup message.
  • Database :- Distribution


         @perfmon_increment = 0
       , @agent_id = 2
       , @runstatus = 1
       , @comments = N'Starting agent.'



Here is the error logged in the SQL Server Agent History.

Error Message

Unable to start execution of step 2 (reason: JobOwner DBLAB\replication  doesn't have 
permissions to use proxy 3 for subsystem Snapshot).  
NOTE: The step was retried the requested number of times (10) without succeeding.  The step failed.

Error Image



Let us isolate the failing statement and issue it ourselves via our favorite SQL Server Query Tool, “Management Studio“. But, let us issue it embedded it in “execute as login” and “revert”. This is due to the fact that the job will run as the “Job Owner”.

execute as login = [domain\account]

    begin tran

        exec distribution.dbo.sp_MSadd_snapshot_history
                  @perfmon_increment = 0
                , @agent_id = 2
                , @runstatus = 1
                , @comments = N'Starting agent.'

    rollback tran


The error message we get is:

Msg 14260, Level 16, State 1, Procedure sp_MSadd_snapshot_history, Line 43
You do not have sufficient permission to run this command.
Contact your system administrator.

SQL Server Profiler

SQL Server Profiler provides a good avenue for identifying the SQL that are being submitted; along with any errors and exceptions that are logged.

Trace Properties

  • Errors and Warnings
    • Exception
    • User Error Message
      • Filter = Error
        • Not Equal to = 5701
        • Not Equal to = 5703
  • Security Audit
    • Audit Login
    • Audit Login Failed
    • Audit Logout
    • Audit Server Principal Impersonation
    • Audit Statement Permission Event
  • Stored Procedures
    • RPC:Completed
    • RPC:Starting
    • SP:Completed
    • SP:Starting
    • SP:StmtCompleted
    • SP:StmtStarting
  • TSQL
    • SQL:BatchCompleted
    • SQL:BatchStarting
    • SQL:StmtCompleted
    • SQL:StmtStarting


Captured Trace

Small Trace

Here is our captured Trace:


Quick Explanation:

We are filtering on the following:

  • SQL:BatchStarting
  • SQL:BatchCompleted
  • User Error Message


  • Text Data = You do not have sufficient permission to run this command. Contact your system administrator.
  • Error = 14260

Bigger Trace


Quick Explanation:

Filtering On:

  • Let us add in
    • SQL:StmtStarting
    • SQL:StmtCompleted
    • SP:Starting
    • SP:StmtStarting
    • SP:StmtCompleted

Captured Data:

  1. The Sql Statement “exec distribution.dbo.sp_MSadd_snapshot_history @perfmon_increment = 0, @agent_id = 2, @runstatus = 1, @comments = N’Starting agent.’ ” is ran
    • The Stored Procedure invoked is distribution.dbo.sp_MSadd_snapshot_history
      • And, the Stored  Procedure checks to see if the account has db_owner privileges in the Distribution database
      • Has it does not, an error us raised via “raiserror(14260, 16, -1)
      • The error message reads “You do not have sufficient permission to run this command. Contact your system administrator.”


We can see that we failed due to the fact that our job owner does not have dbo_owner permission on the distribution database.

use [distribution]

--exec sp_helprolemember @rolename = 'db_owner'

exec sp_addrolemember 'db_owner', 'LABDB\replication'


To get a summary of all the permissions needed, please per-use

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s