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.