授予角色执行存储过程的权限

Question

我有一个代理用户，我想将其添加到一个可以执行所有存储过程的角色中。借助其他 StackOverflow 帖子，我已经能够编写出以下脚本：

USE abc

Create ROLE db_exec
go

GRANT EXECUTE TO db_exec
go

EXEC sp_addrolemember 'db_exec', 'abc_user'
go

尝试运行存储过程时，根据我的错误处理，仍然遇到以下错误：

拒绝对对象'sp_OACreate'的EXECUTE权限，在数据库'mssqlsystemresource'，模式'sys'。

我该怎么做才能让abc_user执行sp_OACreate？

- user3267755

---

sp_OACreate：需要成为sysadmin固定服务器角色的成员。 - Alex K.

@AlexK。我已将用户添加到sysadmin并使用select is_srvrolemember('sysadmin', 'abc_user')进行了验证，返回值为1，但我仍然遇到相同的错误，请问有什么想法吗？ - user3267755

还需要启用OLE自动化，否则在这种情况下也可能会出现错误。最后……尽量避免在SQL Server中使用OLE自动化。它是一个数据库服务器，而不是应用程序服务器。 - RThomas

Answer 1

除了担任系统管理员角色外，您还需要在实际存储这些过程的主数据库上授予执行权限

use master
go

grant exec on sp_OACreate to abc_user
GO

在运行该命令后，您可以使用以下方法验证是否具有执行该过程的权限。

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'

- Daniel Gimenez

---

如果在数据库主服务器上遇到“找不到用户'abc_user'，因为它不存在或您没有权限”的错误，解决方法是在主服务器上为登录创建用户：“create user abc_user for login abc_user;” - CLS

Answer 2

答案是可行的，但通常情况下我们尽可能不会给系统管理员权限任何用户。在这种情况下，我发现运行 sp_OACreate 实际上并不需要 sysadmin 角色。

我运行了以下命令：

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

为了我的需求，我需要一个清理步骤，所以用户需要同时具备创建和销毁的权限。

我希望这能帮助那些想要运行这些程序但不希望用户对服务器上的所有其他数据库具有完全的DB访问权限的人。

-Scott

- Scott V

Answer 3

这些过程位于主控制台的Programmability > Extended Stored Procedures > System Extended Stored Procedures中，如果有帮助的话，请注意。

- BMatic

Answer 4

如果出现以下错误：

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'.

启用 xp_cmdshell 存储过程

此时可能已经执行过了，因此仅供参考：

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

允许用户执行存储过程。

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

检查是否设置了执行权限

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')

- MovGP0