locked
Fixed Agent Roles acting different after restore of MSDB RRS feed

  • Question

  • 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?

    Tuesday, September 14, 2010 5:29 PM

All replies

  • That is peculiar behavior.  Could you check the owners of master and msdb to ensure that they are owned by the same login.  If not, the database chaining between master and msdb may be broken.  Check with:

    SELECT name, SUSER_SNAME(owner_sid)
    FROM sys.databases
    WHERE name in ('master', 'msdb')

    It is quite easy, at restore database time, to wind up with the database owned by the person who did the restore, rather than by 'sa'.  If this is the case, you can:

    USE msdb
    EXEC sp_changedbowner 'sa'

    All the best,
    RLF

    Tuesday, September 14, 2010 6:22 PM
  • Hey Russell,

    Thanks for the quick reply. I did check the database owners when I saw that sp_notify_agent was looking for that & they are both set to sa. How would I check the database chaining between master & msdb?

    Tuesday, September 14, 2010 7:03 PM
  • If you "select name, is_db_chaining_on from sys.databases" you should see how it is set.

    If msdb is not chaining, use "ALTER DATABASE msdb SET DB_CHAINING ON"

    Check the Books Online on ALTER DATABASE.

    RLF

    Tuesday, September 14, 2010 7:27 PM
  • Hey Russell,

    I took a look & the sys.databases view shows both master & msdb as having db_chaining on. My theory, based on limited knowledge of the mssqlresource workings, is that possibly the permissions are not being updated correctly in that database since I'm guessing it is in memory maybe. Not really sure, I know it is a stretch but I'm going to try to restart services then re-add the user & see if that chases the gremlins away. Bummer is whatever time I saved by restoring MSDB instead of just redeploying SSIS & running scripted jobs has evaporated trying to track down this error.

    Thanks again for all your suggestions.

    Wednesday, September 15, 2010 1:39 PM
  • Yes, I understand.  That is a lesson that, over the years, I have had to learn as well.  Shortcuts too often become the long way around.

    RLF

    Wednesday, September 15, 2010 6:35 PM
  • Sorry to bother you but I'm trying to get some advice on whether I should worry about this further. The reboot did not work. I have a work around in place, just added func. acct in public role in master & explicitly granted access to those two ext. procs,  but I'm wondering if I should take the time to re-install SQL Server & just re-deploy the packages & jobs or if I should just roll with what I have?

    Thanks again.

    Thursday, September 16, 2010 2:18 PM
  • True confession: In investigating, I found that I also have one server in a similar state to the oneyou are in now.  It is a 2005 server and I will leave it as is until we move on with the next upgrade. 

    I would say it is up to what you want to live with.  Rolling-on seems pretty low risk.  (But the purist answer, of course, is probably different.)

    RLF

    Thursday, September 16, 2010 5:58 PM
  • Well it is comforting to know I'm not the only person seeing this. I think we are going to just let it be until it's time for SQL 2010. Thanks again for all your help.
    Tuesday, September 28, 2010 1:46 PM
  • Very good.  (Of course, there is no SQL Server 2010.  In fact the next version is still unnamed.)   There is SQL Server 2008 R2, if you would like to upgrade soon.

    RLF

    Tuesday, September 28, 2010 2:16 PM