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
posta un anno fa482 visualizzazioni
1 Risposta
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
con risposta un anno fa

Accesso non effettuato. Accedi per postare una risposta.

Una buona risposta soddisfa chiaramente la domanda, fornisce un feedback costruttivo e incoraggia la crescita professionale del richiedente.

Linee guida per rispondere alle domande