How do I resolve permissions issues with SQL commands and stored procedures on Amazon RDS for SQL Server?

Short description

You might receive permissions errors when you perform the following actions:

• Run system procedures, such as sp_updatestats, sp_configure, and xp_cmdshell to configure and maintain a database.
• Run custom stored procedures.
• Retrieve audit logs.
• Deploy SQL Server Integration Services (SSIS).
• Use SQL Server Management Studio (SSMS).

To maintain service stability, security, and manageability, Amazon RDS or SQL Server doesn't support server-level roles, such as sysadmin. For more information, see Microsoft SQL Server security.

If your workload requires features that standard RDS for SQL Server doesn't support, then use RDS Custom for SQL Server. For full control over the SQL Server database (DB) instance and operating system (OS), use SQL Server on Amazon Elastic Compute Cloud (Amazon EC2). To compare the differences between Amazon EC2 and Amazon RDS for your SQL Server database, see Choosing between Amazon EC2 and Amazon RDS.

Resolution

You receive an error with sp_configure and RECONFIGURE

To manage server-level configurations, such as sp_configure and RECONFIGURE, use DB parameter groups.

Note: Changes in static parameters require you to restart your DB instance for the parameter to take effect. Changes in dynamic parameters, such as max degree of parallelism (MAXDOP), take effect immediately without a restart. For more examples of performance parameters, see Best practices for configuring performance parameters for Amazon RDS for SQL Server.

You receive an error with sp_updatestats

To resolve issues with sp_updatestats, use one of the following workarounds:

• Run the following command to create a wrapper procedure that runs with the dbo permission:

  CREATE PROCEDURE myRDS_updatestats WITH EXECUTE AS 'dbo'  
  AS  
    
  EXEC sp_updatestats  
    
  GO  
  -- Grant execute permission to the required user  
  GRANT EXECUTE ON myRDS_updatestats TO <username>  
  GO

• To update statistics for all tables in a database, run the following command to use UPDATE STATISTICS with Dynamic SQL:

  USE [Database_Name]  
  GO;  
  DECLARE @sql nvarchar(MAX);  
  SELECT @sql = (SELECT 'UPDATE STATISTICS ' +  
                QUOTENAME(s.name) + '.' + QUOTENAME(o.name) +  
                ' WITH FULLSCAN; ' AS [text()]  
         FROM sys.objects o       JOIN sys.schemas s ON o.schema_id = s.schema_id       WHERE o.type = 'U'  
         FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');  
  PRINT @sql  
  EXEC (@sql)  
  GO

• Run the following command to use sp_MSforeachtable:

  EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

If a wrapper procedure fails, then check who owns the database. After failover events occur, you replace hosts, or scale a DB instance class, database ownership can change from rdsa to NT AUTHORITY\SYSTEM.

To resolve this issue, run the following command to change the database ownership back to rdsa:

EXEC msdb.dbo.rds_changedbowner_to_rdsa @dbname = 'YourDatabaseName';

For more information about maintenance tasks, see How do I create SQL Server maintenance tasks in Amazon RDS?

You can't turn on or run xp_cmdshell

Because Amazon RDS is a managed service, you don't have access to the OS and can't run the xp_cmdshell procedure.

Note: The default value for the show advanced options parameter is false, but its run value is set to 1. So, you don't need to run separate SQL commands to use the parameter.

If you must run xp_cmdshell for your workload, then use RDS Custom for SQL Server or Microsoft SQL Server on Amazon EC2.

Error with OLE Automation stored procedures

To protect instance stability and security, the following stored procedures aren't available in Amazon RDS for SQL Server:

• sp_OACreate
• sp_OADestroy
• sp_OAGetErrorInfo
• sp_OAGetProperty
• sp_OAMethod
• sp_OASetProperty
• sp_OAStop

Instead, use RDS Custom for SQL Server that lets you customize the OS and database within the RDS environment.

You can't access SQL Server audit logs

In Amazon RDS for SQL Server, the fn_get_audit_file standard SQL Server function isn't directly accessible. Instead, you must use the msdb.dbo.rds_fn_get_audit_file RDS function.

Example command that uses msdb.dbo.rds_fn_get_audit_file:

SELECT *  
FROM msdb.dbo.rds_fn_get_audit_file(  
    'D:\rdsdbdata\SQLAudit\*.sqlaudit',  
    DEFAULT,  
    DEFAULT  
);

For more information, see Support for SQL Server Audit.

To view audit records in the retention folder, run the following query:

SELECT *  
FROM msdb.dbo.rds_fn_get_audit_file(  
    'D:\rdsdbdata\SQLAudit\transmitted\*.sqlaudit',  
    DEFAULT,  
    DEFAULT  
);

You were denied permission for sp_addmessage

The sp_addmessage system stored procedure requires that you have access to the sysadmin or serveradmin server roles. 

Because Amazon RDS doesn't allow you to use sysadmin or serveradmin, take the following actions instead:

• Use a THROW statement to raise custom exceptions without a requirement to predefine the message in sys.messages:

  THROW 50001, 'Custom error message for the application', 1;

• Use a RAISERROR statement to generate custom error messages. When you don't specify a msg_id, RAISERROR raises an error with error number 50000:

  RAISERROR ('Custom error message', 16, 1);

If your application requires the sysadmin role to run sysadmin or serveradmin, then use Amazon RDS Custom for SQL Server. Or, host SQL Server on an EC2 instance. For more information, see Master user account privileges.

You experienced SSIS package deployment failures

Make sure that the AWS Identity and Access Management (IAM) role that's associated with your DB instance has the required permissions to access the Amazon Simple Storage Service (Amazon S3) bucket. For permissions requirements, see Integrating an Amazon RDS for SQL Server DB instance with Amazon S3.

The DB instance and S3 bucket must be in the same AWS Region. If they are in different Regions, then create a new S3 bucket in the same Region as your DB instance.

For data flow tasks, you must set BLOBTempStoragePath and BufferTempStoragePath to a file path that starts with D:\S3\.

Note: Make sure that all parameters, variables, and expressions that you use for file connections point to the D:\S3 folder.

For more information, see Support for SQL Server Integration Services (SSIS) in Amazon RDS for SQL Server and Integrating an Amazon RDS for SQL Server DB instance with Amazon S3.

Use resource governor

To set specific resource quotas and priorities for different users and applications, use resource governor for control over DB instance resources. You can set specific quotas on how workloads use CPU, memory, and physical I/O resources.

Related Information

Amazon RDS for Microsoft SQL Server

Amazon EC2 for SQL Server

Amazon RDS Custom