AWS/RDS – SQL Server – Error – “The EXECUTE permission was denied on the object ‘agent_datetime’, database ‘msdb’, schema ‘dbo’ “


Here is an error I have been wanting to talk about for a while here.




The agent.date_time function accepts two integer values, date and time.
And, returns the corresponding datetime value.


use [msdb]

declare @date int
declare @time int

set @date = 20190701
set @time = 0

            = [msdb].[dbo].[agent_datetime]
                    , @time


Output – AWS

Here is the result when we issue command against an ASW/RDS MS SQL Server Instance.

Output – AWS – Image

Output – AWS- Textual
Msg 229, Level 14, State 5, Line 10
The EXECUTE permission was denied on the object 'agent_datetime', database 'msdb', schema 'dbo'.

Output – Traditional

Here is expected result.

Output – Traditional – Image

Output – Traditional – Textual
2019-07-01 00:00:00.000

AWS/RDS – Delete Instance


For one of the projects we are standing up in AWS, I will like to test out different Instance configuration options.


It has a big database and I did not want to continue eating the cost of standing up various instances.  It is time to stand down of some of the instances.




Here is the current list of RDS Instances.



  1. dev
    • Development
  2. perf
    • Performance
  3. prod
    • Production

Delete Instance

We are not currently using Prod and so I am going to take it “permanently” down.

And, start running load test against Perf; which is the instance I brought up just yesterday.



  1. Access list of Database Instances
  2. Select instance targeted for deletion
  3. Choose the menu options Action/Delete
  4. If “Delete Protection” has been enabled for the Instance
    • We are prompted to “modify the database and disable deletion protection
  5. Access Instance
    • Review Instance’s “Deletion Protection”
    • Check off “Enable Deletion Protection
    • Save Changes
    • Scheduling of Modifications
      • By default changes are effected during next maintenance window
      • Choose to effect this specific change immediately
  6. Return to list of RDS Databases
  7. Choose to delete instance
    • Prompted as to whether we want to take a final snapshot
    • We made the following modifications
      • We chose not to take that final snapshot
      • Confirm deletion by entering “delete me
    • Advised that RDS Instance Deletion is being processed
  8. List of RDS Databases reflects deletion processed

Screen Images

RDS / Instances / List – 01

This database has deletion protection option enabled



Deletion Protection


Scheduling of Modifications

Apply During the Next Scheduled Maintenance Window

Apply Immediately


RDS / Instances / List – 02

RDS / Instances / Delete Instance ?




RDS / Instances / Deleting Instance

RDS / Instances

SQL Server Agent on AWS/RDS


There is quite a bit of SQL Server management surface area that is not available when running on AWS/RDS.


Surface Area

For instance with SQL Server Agent :-

  1. SQL Server Agent
    • Category
      • Add new category
        • API
          • sp_add_category
        • Error
          • Msg 229, Level 14, State 5, Procedure msdb.dbo.sp_add_category
          • The EXECUTE permission was denied on the object ‘sp_add_category’, database ‘msdb’, schema ‘dbo’.
    • Job Step
      • Fetch Job Steps
        • API
          • SELECT * FROM msdb.dbo.sysjobsteps
        • Error
          • Msg 229, Level 14, State 5, Line 71
          • The SELECT permission was denied on the object ‘sysjobsteps’, database ‘msdb’, schema ‘dbo’.



In terms of scripting, SQL Server Agent is not accessible in an AWS/RDS Environment.

AWS/RDS – SQL Server – Self Identify


Playing around more and more with AWS/RDS.

There is quite a bit of restrictions compared to self hosted platform.

Self Identify

To use some code on premise or cloud hosted, need to sprinkle in conditional statements.



Here are some fingerprints to look for :-

  1. Server Name
    • AWS/RDS
      • EC2AMAZ-N4O
  2. Databases


declare @servername sysname
declare @bAWS       bit
declare @bAWSRDS    bit

set @servername
	= cast(serverproperty('servername') as sysname)

set @bAWS =
	isNull(charindex( 'EC2AMAZ-', @servername), 0)

set @bAWSRDS =
	isNull( (db_id('rdsadmin') ), 0)

print 'server :- ' + @servername
print 'AWS :- ' + cast(@bAWS as varchar)
print 'AWSRDS :- ' + cast(@bAWSRDS as varchar)


Platform – RDS

Platform – RDS – Image – 01