Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
The named job does exist. I'm assuming I just need to grant the user (or perhaps a role I define) the ability to view and execute the job. –  Brian Spolarich Jul 7 '09 at 19:16

1 Answer 1

up vote 0 down vote accepted

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

share|improve this answer
    
That was the problem. Thanks! I thought the SQLAgentUser role was the one I wanted. Now I have a new issue, but I'll ask that separately. –  Brian Spolarich Jul 8 '09 at 19:41
    
No problem. Glad to help. –  mrdenny Jul 8 '09 at 20:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.