Provide a role to executable stored procedures

Question

I have a proxy user that I am trying to add to a role that can execute all stored procedures. Using other StackOverflow posts, I managed to compile 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 start my stored procedures, I still get this error for my error handling.

EXECUTE permission was denied for object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.

What can I do to abc_user execute sp_OACreate ?

Answer 1

In addition to the sysadmin role, you also need to grant permission to execute in the main database, where these procedures are actually located

 use master go grant exec on sp_OACreate to abc_user GO 

After starting, you can confirm the following: 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

In the answers below, we usually try not to grant sysadmin permission to any user whenever possible. In this case, I found that you really don't need the sysadmin role to run sp_OACreate .

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 needed a cleaning step, so the user needed creation and destruction.

I hope that this will help everyone who wants to give the opportunity to run these procedures, but does not want the user to have full access to the database to all other databases on the server.

-Scott

Answer 3

In case of the following errors:

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

Enable xp_cmdshell

this has probably already been done at this point, so this is for reference only:

 EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure 'xp_cmdshell', 1 GO EXEC sp_configure 'show advanced options', 0 GO RECONFIGURE GO 

Allow user to execute stored procedures

 use [master] GO GRANT EXECUTE ON [sys].[xp_cmdshell] TO [DOMAIN\username]; GRANT EXECUTE ON [sys].[sp_OACreate] TO [DOMAIN\username]; GRANT EXECUTE ON [sys].[sp_OADestroy] TO [DOMAIN\username]; GRANT EXECUTE ON [sys].[sp_OAGetErrorInfo] TO [DOMAIN\username]; GRANT EXECUTE ON [sys].[sp_OAGetProperty] TO [DOMAIN\username]; GRANT EXECUTE ON [sys].[sp_OAMethod] TO [DOMAIN\username]; GRANT EXECUTE ON [sys].[sp_OAStop] TO [DOMAIN\username]; GRANT EXECUTE ON [sys].[sp_OASetProperty] TO [DOMAIN\username]; GO 

Check if execution rights are set

 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 = 'DOMAIN\username' WHERE permission_name = 'EXECUTE' AND (so.name = 'xp_cmdshell' OR so.name = 'sp_OACreate' OR so.name = 'sp_OADestroy' OR so.name = 'sp_OAGetErrorInfo' OR so.name = 'sp_OAGetProperty' OR so.name = 'sp_OAMethod' OR so.name = 'sp_OAStop' OR so.name = 'sp_OASetProperty')