SQL Server 2008 v. 10.0.2531
We recently had to expand the C: drive on one of our servers. So we re-installed SQL Server after the expansion & then we just attached all of our user databases, re-mapped logins, & restored the MSDB backup from the old installation. Everything
went well but I'm seeing some weird behavior with regards to the Fixed Agent Roles. A user that was a member of all three roles was getting an error when trying to run a job.
The error was: User does not have access to
xp_sqlagent_is_starting in mssqlresource please grant execute and
xp_sqlagent_notify in mssqlresource please grant execute
I was able to get around it by adding the user to the master DB with public access & granting them execute on these two extended procs but I would really like
to know what happened here. It seems those roles should have access to those procs by default if they are needed to perform the tasks they were intended to perfom. Any idea what I may have done wrong here?
View Complete Post