对存储过程内容的只读访问 [英] Read only access to stored procedure contents

问题描述

是否可以将 SQL Server 设置为向开发人员提供对我们生产数据库中存储过程内容的只读访问权限?

Is it possible to set up SQL Server to give developers read-only access to the contents of stored procedures on our production database?

推荐答案

您可以授予他们 VIEW DEFINITION 权限到这些 proc.
请参阅此处了解此权限的作用.

You can grant them the VIEW DEFINITION privilege to those procs.
See here for what this privilege does.

您可以在不同的范围内应用VIEW DEFINITION:

You can apply VIEW DEFINITION at different scopes:

• 服务器
• 数据库
• 架构
• 单个实体(例如一个过程、一个函数、一个视图)

您还可以使用查询为多个 proc 生成脚本.
所以如果你有一个用户 Bob:

You can also use a query to generate a script for many procs.
So if you have a user Bob:

SELECT N'GRANT VIEW DEFINITION ON '
    + QUOTENAME(SPECIFIC_SCHEMA)
    + N'.'
    + QUOTENAME(SPECIFIC_NAME)
    + N' TO Bob;'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE';

这会给你这样的东西，然后你可以运行:

that will give you something like this, which you can then run:

GRANT VIEW DEFINITION ON [dbo].[aspnet_RegisterSchemaVersion] TO Bob;
GRANT VIEW DEFINITION ON [dbo].[aspnet_CheckSchemaVersion] TO Bob;
GRANT VIEW DEFINITION ON [dbo].[aspnet_Applications_CreateApplication] TO Bob;
...

这篇关于对存储过程内容的只读访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！