February 10, 2012 at 4:53 pm
Hi,
Using SQL Server 2008 R2.
I'm having a wierd problem generating a new snapshot from within SSMS Replication Monitor, where I right click the publication, choosing "Generate Snapshot" (subscriptions are marked for reinitialization). It throws the following error in the snapshot agent history:
Error messages:
Message: You do not have sufficient permission to run this command. Contact your system administrator.
Command Text: sp_MSactivate_auto_sub
Parameters: @publication = xxxPublication
@article = %
@status = initiated
Stack: at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout)
at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.SetPublisherTranSequenceNumViaAutoSub(PublicationActivationState publicationActivationState, SqlConnection connection)
at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.ActivateCSSPublicationAndSetTranSequenceNums(PublicationActivationState publicationActivationState, SqlConnection connection)
at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.ConcurrentPreArticleFilesGenerationTransaction(SqlConnection connection)
at Microsoft.SqlServer.Replication.RetryableSqlServerTransactionManager.ExecuteTransaction(Boolean bLeaveTransactionOpen)
at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.DoConcurrentPreArticleFilesGenerationProcessing()
at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.DoPreArticleFilesGenerationProcessing()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 14260)
Get help: http://help/14260
Server xxx\yyy, Level 16, State 1, Procedure sp_MSupdate_subscription, Line 35
You do not have sufficient permission to run this command. Contact your system administrator. (Source: MSSQLServer, Error number: 14260)
Get help: http://help/14260
I'm using dedicated domain accounts for the snapshot agent, log reader agent and distribution agent. For connections to the subscriber databases I'm using sql server login because they are not within the same domain.
They are configured with the permissions described here: http://msdn.microsoft.com/en-us/library/ms151868(v=sql.105).aspx
I've triple checked all permissions, and everything is correct.
When I start the snapshot agent from the command line, specifying Publisher, Publication, PublisherDB, Distributor, DistributorLogin, DistributorPassword, DistributorSecurityMode, PublisherLogin, PublisherPassword, PublisherSecurityMode and OutputVerboseLevel everything works and the snapshot gets generated. Here's part of the output:
2012-02-10 23:28:02.70 [100%] A snapshot of 55 article(s) was generated.
2012-02-10 23:28:02.70 *************************** Performance Statistics ****************************
2012-02-10 23:28:02.75 Overall snapshot generation time (seconds): 350,16
2012-02-10 23:28:02.75 Total number of rows bulk-copied from published objects: 193867334
2012-02-10 23:28:02.75 Time spent generating schema scripts (seconds): 194,03
2012-02-10 23:28:02.77 Time spent pre-loading database objects for scripting (seconds): 143,54
2012-02-10 23:28:02.77 Time spent analyzing article object dependencies (seconds): 4,99
2012-02-10 23:28:02.77 Time spent customizing article objects for scripting (seconds): 0,23
2012-02-10 23:28:02.77 Time spent resolving duplicate object names (seconds): 0,00
2012-02-10 23:28:02.77 Time spent analyzing foreign key references (seconds): 0,00
2012-02-10 23:28:02.77 Time spent analyzing check and default constraint references (seconds): 0,00
2012-02-10 23:28:02.77 Time spent analyzing non-article object dependencies (seconds): 0,12
2012-02-10 23:28:02.77 Time spent preparing snapshot generation (seconds): 2,34
2012-02-10 23:28:02.77 Time spent bulk copying data (seconds): 320,05
2012-02-10 23:28:02.77 Time spent copying/compressing generated file (seconds): 0,00
2012-02-10 23:28:02.77 Time spent posting snapshot commands (seconds): 27,74
2012-02-10 23:28:02.77 *******************************************************************************
Looking at the snapshot agent job and the "Run agent" step, it's set to run as my snapshot agent account, with the following command:
-Publisher [xxx\yyy] -PublisherDB [xxx] -Distributor [xxx\yyy] -Publication [xxxPublication] -DistributorSecurityMode 1
I think it looks good, so what can the issue be here?
I tried adding -PublisherSecurityMode 1 to the job step command, but the same error occured.
Any help will be much appreciated! Thank you!
February 10, 2012 at 6:36 pm
Just a shot in the dark, Did you try running the SSMS in administrator mode. Right click on SSMS and use the Run as adminstrator option.
-Roy
February 10, 2012 at 7:01 pm
Roy Ernest (2/10/2012)
Just a shot in the dark, Did you try running the SSMS in administrator mode. Right click on SSMS and use the Run as adminstrator option.
Unfortunately, that didn't help. Any other ideas?
February 14, 2012 at 10:48 am
Try to run the snapshot agent job instead of using the replication monitor or any other tool in SSMS.
Blog
http://saveadba.blogspot.com/
February 14, 2012 at 10:57 am
savethytrees (2/14/2012)
Try to run the snapshot agent job instead of using the replication monitor or any other tool in SSMS.
It gives the same result. Already tried that.
February 28, 2012 at 10:15 am
I thought I'd bump this question because I'm still struggling with the problem. Does anyone have any thoughts?
February 28, 2012 at 11:18 am
What rights have you given for the Publisher and the distributor users you are using?
-Roy
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply