sp_OACreate – ‘Ole Automation Procedures’

SQL Script to check which users have sp_OACreate permissions:

SELECT @@SERVERNAME, so.name, usr.name AS LOGIN, class_desc, permission_name, create_date, modify_date
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
WHERE permission_name = ‘EXECUTE’ AND so.name like ‘sp_OA%’

This query will give you a list of all the user permissions for the following:

sp_OAGetProperty
sp_OADestroy
sp_OACreate
sp_OAStop
sp_OAGetErrorInfo
sp_OAMethod
sp_OASetProperty

To Grant a user permission to a specific category:

grant exec on  <sp_OA%> to username