在 Management Studio 中查看表列表所需的权限 [英] Permission required to view list of tables in Management Studio

问题描述

我搜索了一个已经存在的相关问题，但找不到.

I searched SO for a pertinent question existing already but couldn't find one.

我正在为我们服务器场中的几个数据库设置只读数据库角色.以下是一个表的权限示例:

I am setting up a Read-only database role for a few databases in our server farm. Here is a sample of the permissions for one table:

GRANT SELECT ON [dbo].[Table] TO [ReadOnly]
GRANT VIEW DEFINITION ON [dbo].[Table] TO [ReadOnly]
DENY ALTER ON [dbo].[Table] TO [ReadOnly]
DENY CONTROL ON [dbo].[Table] TO [ReadOnly]
DENY DELETE ON [dbo].[Table] TO [ReadOnly]
DENY INSERT ON [dbo].[Table] TO [ReadOnly]
DENY REFERENCES ON [dbo].[Table] TO [ReadOnly]
DENY TAKE OWNERSHIP ON [dbo].[Table] TO [ReadOnly]
DENY UPDATE ON [dbo].[Table] TO [ReadOnly]

这适用于 SELECT 权限......我能够ONLY SELECT 数据，这正是我想要的.

This works as intended for the SELECT permissions...I'm able to ONLY SELECT data, which is exactky what I want.

但是，我在 Management Studio 中看不到指定数据库的表"选项卡中的表列表.我对这个用户的目标是为一些不熟悉 SQL 的用户提供一个登录名，他们可以用来提取数据并开始试验 SQL.这些用户在 SAS(一种统计处理语言)方面有丰富的经验，因此他们有一些使用代码的经验，但特别是在 SQL 方面没有那么多经验.

However, I cannot see the list of tables in the "Tables" tab for the specified database in Management Studio. My goal with this user is to provide some users that are unfamiliar with SQL a login they can use to pull data and start experimenting with SQL. These users are experienced with SAS, a statistical processing language, so they have some experience working with code but not so much specifically in SQL.

哪些权限会​​显示 Management Studio 中的表列表?

Which of those permissions will show the list of tables in Management Studio?

推荐答案

您只需要授予任何权限和视图定义即可，但是您还明确DENY来自同一用户的可继承权限(DENY 总是覆盖 GRANT).如果您只是没有 GRANT 权限，他们将不会拥有它(或者，您可以 REVOKE 而不是 DENY，后者不会显式覆盖 GRANT代码>).如果您将 DENY 更改为 REVOKE 在上述脚本中，您的用户将能够在 SSMS 中列出/查看对象.

Granting any permission and view definition is all you need, however you're also explicitly DENYing inheritable permissions from the same user (DENY always overrides a GRANT). If you simply do not GRANT the permission, they will not have it (alternatively, you can REVOKE rather than DENY, which does not explicitly override a GRANT). If you change your DENY's to REVOKE in the above script, your users will be able to list/view the objects in SSMS.

这篇关于在 Management Studio 中查看表列表所需的权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！