The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo' despite Trustworthy Set to ON

Question

Post reply

The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo' despite Trustworthy Set to ON

TheComedian

SSCrazy

Points: 2932
More actions
November 18, 2015 at 8:22 am

#302426

Hi all, I'm having a problem here and I don't know what's wrong because it worked in my development environment but test is a disaster. I'm using SQL Server 2014 and I'm getting this error:
The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.
I have the following stored procedure:
USE [ApplicationDatabase]
GO
CREATE PROCEDURE [dbo].[SelectJobs]
with EXECUTE AS 'OFFICE\ProxyUser'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT *
FROM msdb.dbo.sysjobs
END
Both ApplicationDatabase and msdb have been set to trustworthy = 1 (I know the risks). I've also elevated OFFICE\ProxyUser to dbowner in msdb and ApplicationDatabase (just to see what's wrong) but the error persists. I've also set DB_CHAINING to ON for both databases and still it stubbornly refuses to execute that SELECT statement.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Check for an explicit deny right on the sysjobs table

TheComedian SSCrazy Points: 2932 More actions · Answer 2

Thanks for the quick response but I've already done that (sorry for failing to mention it originally) and there is none.

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

Can you run SSMS as the proxy user and try running the select?

Are there no deny's at all on MSDB.dbo.sysjobs?

The proxy account inst part of any groups which have deny data writer db role?

TheComedian SSCrazy Points: 2932 More actions · Answer 4

There's no DENY on the table, the schema or the database. The user is not part of any group and if I login as the Proxy User, I can SELECT that table, just not when I execute the SP, which is messed up because they're both set to TRUSTWORTHY.

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

So its down to cross database issues.

Are both DB's owned by the same account?

TheComedian SSCrazy Points: 2932 More actions · Answer 6

That was it; thanks a lot for your help. The owner of the msdb database was sa and the application database was owned by a user that left the organization 2 years ago.