Permission to execute procedure - SQL

0

I have an RDS instance, with MS SQL Server database.

I need to grant permissions to a user in my application to execute some important procedures. However, even connected to the RDS instance with the admin user, I cannot grant these permissions.

The query I need to run is the following:

USE [master]
GO
sp_configure 'Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE
GO
GRANT EXECUTE ON sys.sp_OACreate TO [usr_apis]
GRANT EXECUTE ON sys.sp_OADestroy TO [usr_apis]
GRANT EXECUTE ON sys.sp_OAGetErrorInfo TO [usr_apis]
GRANT EXECUTE ON sys.sp_OAGetProperty TO [usr_apis]
GRANT EXECUTE ON sys.sp_OAMethod TO [usr_apis]
GRANT EXECUTE ON sys.sp_OASetProperty TO [usr_apis]
GRANT EXECUTE ON sys.sp_OAStop TO [usr_apis]

and when running the query above, the following errors are returned:

Msg 15247, Level 16, State 1, Procedure sp_configure, Line 105 [Batch Start Line 2]
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Line 5
You do not have permission to run the RECONFIGURE statement.
Msg 15247, Level 16, State 1, Procedure sp_configure, Line 105 [Batch Start Line 6]
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Line 9
You do not have permission to run the RECONFIGURE statement.
Msg 15151, Level 16, State 1, Line 11
Cannot find the user 'usr_apis', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 12
Cannot find the user 'usr_apis', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 13
Cannot find the user 'usr_apis', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 14
Cannot find the user 'usr_apis', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 15
Cannot find the user 'usr_apis', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 16
Cannot find the user 'usr_apis', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 17
Cannot find the user 'usr_apis', because it does not exist or you do not have permission.

How can I grant the user of my application the necessary permissions to execute these procedures?

Thanks.

profile picture
asked a year ago442 views
1 Answer
0

To deliver a managed service experience, Amazon RDS restricts access to certain system objects and procedures that require advanced privileges, including the "primary" database. Some system tools such as sp_configure are not available as a way to set server level settings. You'll need to manage your database engine configuration through parameter groups. OLE Automation isn’t supported either on RDS. If OLE Automation is a strong requirement then you’ll need to run a self-managed SQL Server on EC2.

AWS
NZ
answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions