SQL Server 2005“公共"数据库角色似乎不适用? [英] SQL Server 2005 "public" database role doesn't seem to apply?

问题描述

我有一个 SQL Server 2005 数据库，我尝试使用 Windows 身份验证以受限用户帐户的身份访问该数据库.我已将 BUILTINUsers 添加为数据库用户(在此之前，我什至无法打开数据库).我的工作假设每个人都应该拥有适用于他们的公共"角色的权限，所以我没有对角色分配做任何事情.在 tblFoo 下，我可以使用 SSMS 属性对话框(权限页面)添加公共"，然后设置显式权限.其中包括 SELECT 的Grant".但是运行

I have a SQL Server 2005 database that I'm trying to access as a limited user account, using Windows authentication. I've got BUILTINUsers added as a database user (before I did so, I couldn't even open the database). I'm working under the assumption that everybody is supposed to have permissions for the "public" role applied to them, so I didn't do anything with role assignment. Under tblFoo, I can use the SSMS Properties dialog (Permissions page) to add "public", then set explicit permissions. Among these is "Grant" for SELECT. But running

SELECT * from tblFoo;

作为受限(BUILTINUsers)帐户给我一个错误选择对象'tblFoo'、数据库'bar'、架构'dbo'的权限被拒绝".在属性对话框中，有一个有效权限"按钮，但它是灰色的.

as a limited (BUILTINUsers) account gives me an error "Select permission denied on object 'tblFoo', database 'bar', schema 'dbo'". In the properties dialog, there's an "Effective Permissions button, but it's greyed out.

此外，我尝试创建一个名为UserTest"的非私有帐户，在服务器级别添加该帐户，然后将其映射到bar"数据库.这让我将 UserTest 添加到用户或角色"列表中，这让我可以为帐户运行有效权限".根本没有列出任何权限——这似乎不对.该帐户必须是公共的，并且公共授权(除其他外)在 tblFoo 上选择，那么为什么 UserTest 帐户没有显示有效权限?我觉得我在这里有点发疯了.

Further, I tried creating a non-priv account called "UserTest", adding that at the server level, then mapping it down to the "bar" database. This let me add UserTest to the "Users or Roles" list, which let me run "Effective Permissions" for the account. No permissions are listed at all -- this doesn't seem right. The account must be in public, and public grants (among other things) Select on tblFoo, so why doesn't the UserTest account show an effective permission? I feel like I'm going a bit crazy here.

旁白:我知道很多人不喜欢使用public"角色来设置权限.这只是我的修补时间；在最终设计中，我确信我们会有几个灵活的(自定义)数据库角色.我只是想弄清楚我看到的行为，所以请不要不要那样做！"答案.

ASIDE: I am aware that many people don't like using the "public" role to set permissions. This is just my tinkering time; in final design I'm sure we'll have several flexible (custom) database roles. I'm just trying to figure out the behavior I'm seeing, so please no "don't do that!" answers.

更新:显然我对 SQL Server 的了解足以对我自己和他人构成威胁.在设置权限时(正如我所说，除其他外")，我有拒绝控制.当我设置这个权限时，我想我试图查看它做了什么，有一个模糊的想法，并决定拒绝.我目前不记得为什么这似乎是要做的事情，但似乎这就是我获得许可失败的原因.所以我要更新我的问题:任何人都可以解释控制"权限，因为它与表格有关?

UPDATE: Apparently I know just enough SQL Server to be a danger to myself and others. In setting permissions (as I said, "among others"), I had DENY CONTROL. When I set this permission, I think I tried to look up what it did, had a vague idea, and decided on DENY. I cannot currently recall why this seemed the thing to do, but it would appear that that was the reason I was getting permission failures. So I'm updating my question: can anyone explain the "CONTROL" permission, as it pertains to tables?

推荐答案

您只需要拥有 SELECT 权限.在原始 SQL 中(参见对话框中的脚本"图标/按钮)，它是 GRANT SELECT ON dbo.tblFoo to public.这是查看数据所需的唯一权限，

You only need to have SELECT rights. In raw SQL (see the "script" icon/button in your dialogue box), it's GRANT SELECT ON dbo.tblFoo to public. This is the only permission needed to view the data,

在这种情况下，错误消息明确提到拒绝".拒绝"本身就是一种权利，所以它提到了它，

In this case, the error message explicitly mentions "deny". "DENY" is a right in itself, so it mentions it,

如果您没有权限，您将收到消息(非常近似)tblFoo 不存在或您没有权限"

If you had no rights, you'd get the message (very approximately) "tblFoo does not exist or you do not have rights"

拒绝控制"在此处.在这种情况下，您拒绝了公共角色的所有权利.

"DENY CONTROL" is mentioned here. In this case, you denied all rights to the public role.

受让人实际上拥有所有对安全对象的定义权限

The grantee effectively has all defined permissions on the securable

这篇关于SQL Server 2005“公共"数据库角色似乎不适用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！