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

Background

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

Code

msdb.dbo.agent_datetime

Outline

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

SQL


use [msdb]
go

declare @date int
declare @time int

set @date = 20190701
set @time = 0

select
        [ts]
            = [msdb].[dbo].[agent_datetime]
                (
                      @date
                    , @time
                )

Output

Output – AWS

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

Output – AWS – Image

agent_datetime.aws.ouput.20190705.1257pm

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

agent_datetime.aws.output.good.20190705.0101pm.PNG

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

AWS/RDS – Delete Instance

Background

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

Constraints

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.

 

Instances

Current

Here is the current list of RDS Instances.

Image

Text

  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.

 

Outline

  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

 

Instance

Deletion Protection

 

Scheduling of Modifications

Apply During the Next Scheduled Maintenance Window

Apply Immediately

 

RDS / Instances / List – 02

RDS / Instances / Delete Instance ?

Initial

Final

 

RDS / Instances / Deleting Instance

RDS / Instances

SQL Server Agent on AWS/RDS

Background

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’.

 

Scripting

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

AWS/RDS – SQL Server – Self Identify

Background

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.

Code

Outline

Here are some fingerprints to look for :-

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

SQL


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)

Output

Platform – RDS

Platform – RDS – Image – 01

selfIdentify.AWSRDS.20190205.1202PM