SQL Job Step - different job owner and step user

0
votes

I'd like to start steps under security context of user (execute as), but it's not working when job owner is different user than "Run as user".

Executed as user: XXXX. The SELECT permission was denied on the object 'sysschedules', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229). The step failed.

But even grant select for msdb objects sysschedules, sysjobs, sysjobactivity didn't work. Even server sysadmin role didn't work. Event adding into role SQLAgentUserRole, SQLAgentReaderRole or/and SQLAgentOperatorRole didn't work. Still the same error.

What are minimal permissions/roles for user to execute t-sql step where job is owned by different user?

execute as user = N'USERNAME' -- not working
select count(*) from msdb.dbo.sysschedules

User is member of db_owner. User have permission control, select... on dbo schema.

1
sql-server

1 Answers

0
votes

There was problem when I'd like to select from msdb.dbo.sysschedules but from my database. User exists on database msdb too. Problem was fixed by adding option TRUSTWORTHY Database property - now can the same user from my database be used in msdb.

Whole problem was based on procedure msdb.dbo.sp_add_jobstep parameter @database_user_name - it's not login, but only user.

TRUSTWORTHY Database property