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

Background

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.

Preface

There are a few SQL Server Agent jobs created.

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

  • Category :- REPL-Snapshot

Job-Repl

Here is the Job Step:

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

Command:


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

JobStep-SnapshotAgentStartupMessage

Error

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

SQLServerAgent


Diagnostic

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

revert
go

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

TraceProperties

Captured Trace

Small Trace

Here is our captured Trace:

SQLServerProfiler-ErrorMessage

Quick Explanation:

We are filtering on the following:

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

Captured

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

Bigger Trace

SQLServerProfiler-Large

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

Resolution

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]
go 

--exec sp_helprolemember @rolename = 'db_owner'
--go 

exec sp_addrolemember 'db_owner', 'LABDB\replication'
go 

Summary

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s