SQL Server - 执行存储过程只需角色

Question

如何创建一个自定义的 SQL Server 数据库服务器角色，该角色只能运行 SELECT 查询和存储过程？

这意味着，该角色的用户将不允许执行自定义查询，但可以运行包含 CRUD 和 SysAdmin 语句（UPDATES、DELETES、ALTERS、DROPS）的存储过程。

我尝试创建此自定义角色，但在运行更改表的存储过程时失败了。

CREATE ROLE SupportStaff
GRANT SELECT TO SupportStaff
GRANT EXECUTE TO SupportStaff

有什么想法吗？

更新

好的，我发现上面的代码允许带有插入/更新/删除语句的存储过程。但它不允许 ALTER、TRUNCATE 或 DROP INDEX 语句。

对于 ALTER，我只需要添加 GRANT ALTER TO SupportStaff

但是我需要做什么来允许 TRUNCATE 和 DROP INDEX 呢？

- Rafferty

---

当您运行更新时，您是作为SA还是dbo组中的某个人登录的？您不能更改您没有所有权的表。 - Brian

我使用了一个新的SQL登录，并将角色“SupportStaff”添加到这个新用户。 - Rafferty

嗨Brian，我更新了我的问题。GRANT ALTER允许我修改一个表，不知道你是否知道如何允许TRUNCATE和DROP INDEX呢？ - Rafferty

根据我所读的，ALTER应该适用于删除索引和截断。祝你好运……今天是星期五，我要回家了。 - Brian

Answer 1

创建一个角色并将其作为db_datareader的成员，然后逐个为每个存储过程添加EXECUTE权限。以下是一个示例，其中用户称为test并且是该角色的成员。以管理员身份运行此命令：

CREATE TABLE test (id INT)
GO

CREATE PROCEDURE INSERTtest
AS
begin
INSERT INTO dbo.test
        (id)
VALUES
        (1)
END
GO  

GRANT EXECUTE ON dbo.INSERTtest TO test
GO

如果您的进程将数据插入到表中，并且它们不会破坏对象的所有权链，则可以使用此设置。请尝试使用用户进行此操作：

SELECT * FROM dbo.test --sucess
INSERT INTO dbo.test(id)VALUES(1) -- fail
EXEC INSERTtest  --sucess

- Diego