Grant role to exec stored procedures

Question

I have a proxy user that I'm trying to add to a role that can execute all stored procedures. Using other StackOverflow posts, I have been able to put together this script

USE abc

Create ROLE db_exec
go

GRANT EXECUTE TO db_exec
go

EXEC sp_addrolemember 'db_exec', 'abc_user'
go

When I try to run my stored procedures though, I'm still getting this error, per my error handling.

The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.

What can I do to let abc_user execute sp_OACreate?

Answer 1

In addition to being in sysadmin role, you also need to grant execute permission on the master database where those procedures actually reside

use master
go

grant exec on sp_OACreate to abc_user
GO

After you run that you can verify with the following that you have permission to execute the procedure

SELECT * 
FROM master.sys.database_permissions [dp] 
JOIN master.sys.system_objects [so] ON dp.major_id = so.object_id
JOIN master.sys.sysusers [usr] ON 
     usr.uid = dp.grantee_principal_id AND usr.name = 'abc_user'
WHERE permission_name = 'EXECUTE' AND so.name = 'sp_OACreate'

Answer 2

The answer given works, however, we generally try to not give the sysadmin permission to any user whenever possible. In this case I have found to run sp_OACreate you don't actually need the sysadmin role.

I ran the following:

use master
grant exec on sp_OACreate to yourSecObject
grant exec on sp_OADestroy to yourSecObject  --Optional
grant exec on sp_OAMethod to yourSecObject

For my purposes I required a cleanup step so the user required both Create and Destroy.

I hope this helps anyone who wants to give the ability to run these procedures but does not want the user to have full DB access to all other databases on the server.

-Scott

Answer 3

The procs are located in master > Programmability > Extended Stored Procedures > System Extended Stored Procedures if that helps.