SQL Server 2005 中的内置数据库角色允许执行存储过程吗？

发布于 2024-08-21 20:07:10 字数 261 浏览 6 评论 0原文

在 SQL Server 2005 中，有内置的角色：

db_datareader
db_datawriter
等等

是否有任何角色可以让用户执行存储过程？

我不想使用 db_owner，因为这将允许删除和更新，而我不需要。我唯一需要的权限是：

选择
执行

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

得不到的就毁灭 2024-08-28 20:07:10

看看这篇文章。它可能会为您提供一个有趣的想法来快速完成此操作。

该文章中使用的代码：

/* Create a new role for executing stored procedures */
CREATE ROLE db_executor

/* Grant stored procedure execute rights to the role */
GRANT EXECUTE TO db_executor

/* Add a user to the db_executor role */
EXEC sp_addrolemember 'db_executor', 'AccountName'

Take a look at this article. It may provide you an interesting idea to do this quickly.

Code used in that article:

/* Create a new role for executing stored procedures */
CREATE ROLE db_executor

/* Grant stored procedure execute rights to the role */
GRANT EXECUTE TO db_executor

/* Add a user to the db_executor role */
EXEC sp_addrolemember 'db_executor', 'AccountName'

回复收藏 0 原文

享受孤独 2024-08-28 20:07:10

CREATE ROLE db_executor

GRANT EXECUTE TO db_executor

现在，如果您重新启动 SQL Server Management Studio，当您单击“安全”->“登录”部分中的“用户映射”页面时，您将看到“db_executor”出现在角色列表中。只需添加用户，或者您可以手动执行此操作：

EXEC sp_addrolemember 'db_executor', 'AccountName'

CREATE ROLE db_executor

GRANT EXECUTE TO db_executor

Now, if you restart SQL Server Management Studio, when you click on the "User Mapping" page in the Security->Logins section, you'll see "db_executor" appear in the roles list. Just add the user or you can do it manually:

EXEC sp_addrolemember 'db_executor', 'AccountName'

回复收藏 0 原文

烈酒灼喉 2024-08-28 20:07:10

不，我不认为存在数据库或服务器角色 - 您必须向用户精细地授予相关存储过程的执行权限。

回复收藏 0 原文

枯叶蝶 2024-08-28 20:07:10

为了扩展答案，一般要点是创建一个数据库角色并向该角色分配权限。为此，您需要一些奇特的动态 SQL，例如：

Set @Routines = Cursor Fast_Forward For
    Select ROUTINE_SCHEMA + '.' + ROUTINE_NAME, ROUTINE_TYPE, DATA_TYPE
    From INFORMATION_SCHEMA.ROUTINES
    Where ROUTINE_NAME NOT LIKE 'dt_%'
        Or ROUTINE_TYPE = 'FUNCTION'

Open @Routines
Fetch Next From @Routines Into @Procname, @RoutineType, @DataType

While @@Fetch_Status = 0
Begin
    Set @Msg = 'Procname: ' + @Procname + ', Type: ' + @RoutineType + ', DataType: ' + Coalesce(@DataType,'')
    Raiserror(@Msg, 10, 1) WITH NOWAIT

    If @RoutineType = 'FUNCTION' And @DataType = 'TABLE'
        Set @SQL = 'GRANT SELECT ON OBJECT::' + @Procname + ' TO StoredProcedureDataReader'
    Else
        Set @SQL = 'GRANT EXECUTE ON OBJECT::' + @Procname + ' TO StoredProcedureDataReader'

    exec(@SQL)

    Fetch Next From @Routines Into @Procname, @RoutineType, @DataType
End

Close @Routines
Deallocate @Routines

此代码将 EXECUTE 授予存储过程和标量函数，并将 SELECT 授予返回 TABLE 类型的用户定义函数。

To expand on the answer, the general gist is to create a database role and assign permissions to that role. In order to do that, you need some fancy dynamic SQL such as:

Set @Routines = Cursor Fast_Forward For
    Select ROUTINE_SCHEMA + '.' + ROUTINE_NAME, ROUTINE_TYPE, DATA_TYPE
    From INFORMATION_SCHEMA.ROUTINES
    Where ROUTINE_NAME NOT LIKE 'dt_%'
        Or ROUTINE_TYPE = 'FUNCTION'

Open @Routines
Fetch Next From @Routines Into @Procname, @RoutineType, @DataType

While @@Fetch_Status = 0
Begin
    Set @Msg = 'Procname: ' + @Procname + ', Type: ' + @RoutineType + ', DataType: ' + Coalesce(@DataType,'')
    Raiserror(@Msg, 10, 1) WITH NOWAIT

    If @RoutineType = 'FUNCTION' And @DataType = 'TABLE'
        Set @SQL = 'GRANT SELECT ON OBJECT::' + @Procname + ' TO StoredProcedureDataReader'
    Else
        Set @SQL = 'GRANT EXECUTE ON OBJECT::' + @Procname + ' TO StoredProcedureDataReader'

    exec(@SQL)

    Fetch Next From @Routines Into @Procname, @RoutineType, @DataType
End

Close @Routines
Deallocate @Routines

This code will grant EXECUTE to stored procedures and scalar functions and SELECT to user-defined functions that return a TABLE type.

回复收藏 0 原文

~没有更多了~