SQL Server-仅执行存储过程角色 [英] SQL Server - Execute Stored Procedure Only Role

问题描述

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

How do I create a custom SQL Server database server role that can only run SELECT queries and stored procedures?

意思是，不允许该角色的用户执行自定义查询，但可以运行具有CRUD和SysAdmin语句的存储过程-UPDATE，DELETES，ALTERS，DROPS。

Meaning, users of this role won't be allowed to do custom queries, but can run stored procedures that has CRUD and SysAdmin statements -- UPDATES, DELETES, ALTERS, DROPS.

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

I tried creating this custom role, but failed when I ran an SP that alters a table.

CREATE ROLE SupportStaff
GRANT SELECT TO SupportStaff
GRANT EXECUTE TO SupportStaff

有什么想法吗？

好，所以我发现上面的代码允许存储过程使用INSERT / UPDATE / DELETE语句。但这不允许ALTER，TRUNCATE或DROP INDEX语句。

Okay, so I found that the above code allows Stored Procedures with INSERT/UPDATE/DELETE statements. But it doesn't allow ALTER, TRUNCATE or DROP INDEX statements.

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

For ALTER, I simply need to add GRANT ALTER TO SupportStaff

但是我需要做什么才能允许 TRUNCATE 和 DROP INDEX ？

But what do I need to do to allow TRUNCATE and DROP INDEX?

推荐答案

创建角色并将其设置为db_datareader的成员，然后向每个过程添加EXECUTE权限个别地。用户名为test且该角色的成员的示例。以管理员身份运行：

create a role and make it member of db_datareader then add EXECUTE permission to each procedure individually. Example with an user called test and member of that role. Run this as an admin:

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

如果您的处理程序将数据插入表中，并且它们没有破坏对象的所有权链，则此设置应该可以。与用户一起尝试：

If your procs inset data into the tables, and they don't break the object's ownership chain, you should be fine with this set up. Try this with the user:

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

这篇关于SQL Server-仅执行存储过程角色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！