August 27, 2009 at 8:44 am
Hi All,
I've created a login called 'monitor' which has a mapping to the msdb database. It has role membership in the following:
db_datareader, db_owner, and public. The default DB for the login 'monitor' is a reporting DB, not MSDB.
Management studio shows that within MSDB database the user 'monitor' has membership in db_datareader and db_owner.
Now from a new query window using the reporting database, I can execute the following successfully:
execute as user = 'monitor'
use msdb;
GO
Then when I try to execute the following, I get the error listed in the description. That is, executing select top 5 * from sysjobactivity results in:
"The SELECT permission was denied on the object 'sysjobactivity', database 'msdb', schema 'dbo'."
I'm not sure what I'm missing to get this simple query to work. Any help is greatly appreciated.
Thanks!
August 27, 2009 at 9:07 am
It's possible that permissions are not granted on that table from those roles. It's a system table, so I'd expect that db_datareader wouldn't, but I wouldn't think db_owner would not. However, maybe that's the case.
Have you checked into some of the other Agent roles? (http://www.mssqltips.com/tip.asp?tip=1041)
August 27, 2009 at 9:29 am
I added that user to the sql agent roles as you suggested but to no avail, I still get the same denied message.
October 7, 2009 at 10:05 am
try
alter database
set trustworthy ON
(has its own pros and cons though)
October 7, 2009 at 4:44 pm
I am able to run it with datareader role membership alone. Check for an EXPLICIT deny then.
MJ
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply