Home / server / Questions / 37233
Accepted
Brian Spolarich
Asked: 2009-07-08 10:25:36 +0800 CST

Safely Grant Access to SQL 2005 Replication Monitor to Create Snapshots

  • 772

0 vote down star

I have a database that I'm publishing to a remote site using Snapshot replication (only method that will work given the application and database schema). Because the replica database tables are unavailable while the database is refreshing, I am taking and delivering snapshots twice daily. For the target user population this is okay 99% of the time.

Occasionally they might want to manually kick off a snapshot so the replica will get updated. My question is how to I grant permission in MSSQL 2005 so the user can do this?

I've added my test account to the SQLAgentUser role in msdb and to the replmonitor role in the distribution database. This lets me run Replication Monitor and see the publication. When I try to run "Generate Snapshot" I get this error:

"The specified @job_name ('VULCAN-MfgSys803-Vantage8-ProdGLData-3') does not exist. (Microsoft SQL Server, Error 14262)"

I tried granting myself EXECUTE rights to xp_sqlagent_enum_job in master but that didn't help either.

sql-server-2005 security replication monitoring

1 Answers

  1. Best Answer

    Replication is controlled via jobs. Add the needed account to the SQLAgentOperator role in the msdb database. This should take care of the issue.

    • 0