1

I don't like working in RDS. I can't even query sysjobschedules with my Master user

SSMS error message

This data isn't completely gated off from me. For example I can see the schedule data in the job management UI.

SSMS Manage Schedules interface

I can also retrieve this same data using SMO.

$SqlInstance = Connect-DbaInstance sql01 $AllJobs = $SqlInstance.JobServer.Jobs $AllJobs[0].JobSchedules

If I profile the above actions, I see (in part) this query which calls out to sp_help_jobschedule. Following from there, I can do something like execute sp_help_jobschedule @schedule_id = 92 and get back a valid resultset.

I don't understand why this works. AFAICT, one of the relevant code blocks in sp_help_jobschedule is...

 IF (@schedule_id IS NOT NULL) AND (@job_id IS NULL) AND (@job_name IS NULL) BEGIN SELECT @job_count = COUNT(*) FROM msdb.dbo.sysjobschedules WHERE (schedule_id = @schedule_id) -- ... SELECT @job_id = job_id FROM msdb.dbo.sysjobschedules WHERE (schedule_id = @schedule_id) -- .... END

...and as we've already established, I cannot query this base object.

I do not see any execute as declaration in the sp_help_jobschedule source code and the only role grants for my RDS Master user are for the builtin defaults SQLAgentOperatorRole & SQLAgentUserRole neither of which appear to be modified from default settings nor have direct grants against either sp_help_jobschedule or sysjobschedules.

Wtf am I missing? How am I accessing base job schedule data in RDS?

Edit: this was PEBKAC. There was a direct grant against SQLAgentUserRole.

Checking sys.database_perms in RDS with this plagiarized query shows a grant against sp_help_jobschedule to SQLAgentUserRole which I'd missed previously. This grant is also present in my unmodified laptop instance.

Improve this question

1 Answer 1

Reset to default
1

How am I accessing base job schedule data in RDS?

I don't have any RDS items to play with so you'll need to verify, however ownership chaining easily explains the issue. There may be other factors at play since this is RDS, though, but you can repro the same issue with any box release instance.

Example

Create a login called LowPrivLogin with no associated database users. Creates a job called TestJob that does nothing but has a schedule so that data exists in the underlying system catalog tables.

USE master GO CREATE LOGIN LowPrivLogin WITH PASSWORD = N'SomeP@ssw0rd!' GO USE msdb GO DECLARE @jobId BINARY(16) EXEC msdb.dbo.sp_add_job @job_name=N'TestJob', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'No description available.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'LowPrivLogin', @job_id = @jobId OUTPUT EXEC msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'syspolicy_purge_history_schedule', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=1, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20080101, @active_end_date=99991231, @active_start_time=20000, @active_end_time=235959, @schedule_uid=N'a0ed555e-1fad-488c-9209-100e0724eacd' EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

By default, there are no permissions to select or execute anything, this should fail.

EXECUTE AS LOGIN = 'LowPrivLogin' SELECT SUSER_SNAME() AS LoginName -- Nothing extra has been given so by default has public SELECT name, usage FROM sys.login_token -- Should fail, no permissions SELECT * FROM msdb.dbo.sysjobschedules -- Should also fail, no permissions EXEC msdb.dbo.sp_help_jobschedule @job_name = N'TestJob' REVERT

However, since the stored procedures and catalogs are owned by the same principal, ownership chaining can be utilized.

USE msdb GO -- Grant execute to public, not a good idea but just for demo purposes GRANT EXECUTE ON sp_help_jobschedule TO public

Now, executing the previous test...

EXECUTE AS LOGIN = 'LowPrivLogin' SELECT SUSER_SNAME() AS LoginName -- Nothing extra has been given so by default has public SELECT name, usage FROM sys.login_token -- Should fail, no permissions SELECT * FROM msdb.dbo.sysjobschedules -- Should work, ownership chaining kicks in EXEC msdb.dbo.sp_help_jobschedule @job_name = N'TestJob' REVERT
Improve this answer
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.