December 8, 2008 at 11:56 am
On Friday we all our databases from a 32bit server to a 64bit one. I moved all of our database including msdb. Now I am getting the following error when members of the SQLAgentOperators Role try to view the jobs on the server:
EXECUTE permission denied on object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'
There appears to be permissions lost to the Resource database which I presume occurred because msdb came from a different server. I read this could be caused because master and msdb have different owners so I changed to owner of msdb to sa again using the following:
USE MSDB
Go
EXEC sp_changedbowner 'sa'
Go
However, this did not work. I can correct the problem by adding the users to the master database and granting execute permissions on the stored procedure but this wasn't necessary on the old server so I would like to avoid doing this.
Does anyone have any ideas on how to fix this?
December 10, 2008 at 1:03 am
If anyone else has a similar issue I was able to fix it.
I was hoping that buy installing the cumulative update pack for sp2 would force the resource database to be rebuilt and the permissions to be reset. I installed the update pack tonight and it worked like a charm. So that may be a possibility for someone if you're having the same problem.
December 10, 2008 at 3:15 am
You cannot use sp_changedbowner on system databases. The only way of changing the ownerback to sa is reattach the msdb while being logged on as sa. Unfortuantely it's not a simple dettach/attach. You have to follow the same process as if you want to move the msdb to a different location. That means start the server in single user mode with trace flag 3608, then log on as sa and then detach .attach the database.
Here you can find a more detailed description http://deepakrangarajan.blogspot.com/2007/08/moving-system-databases-in-sql-2005.html
Anyhow it's always a very bad idea to restore system databases to another server. Every thing in the msdb you might want to transfer to another server like jobs, operators, alerts, packages can easily be transferred using SSIS.
[font="Verdana"]Markus Bohse[/font]
December 10, 2008 at 8:57 am
You can use sp_changedbowner to change the owner of msdb because I did it. You cannot use it to change the owner of master, model, tempdb because I got an error. Please see the following article. It does not mention that the owner of msdb cannot be changed:
http://msdn.microsoft.com/en-us/library/aa259622.aspx
But thanks for your help
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply