CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
user3267755
user3267755

Reputation: 1080

Grant role to exec stored procedures

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?

Upvotes: 11

Views: 61441

Answers (4)

BMatic
BMatic

Reputation: 11

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

Upvotes: 1

MovGP0
MovGP0

Reputation: 7792

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 the xp_cmdshell procedure

it was probably already be executed 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 the user to execute the 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 the execute permissions 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')

Upvotes: 3

Scott V
Scott V

Reputation: 61

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

Upvotes: 6

Daniel Gimenez
Daniel Gimenez

Reputation: 20654

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'

Upvotes: 16

Related Questions