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

问题描述

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

I have a SQL Server 2005 database that I'm trying to access as a limited user account, using Windows authentication. I've got BUILTIN\Users 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;

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

as a limited (BUILTIN\Users) 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.

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

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会对自己和他人构成危险.在设置权限时(如我所说，还有其他")，我拥有DENY CONTROL.当我设置此许可时，我想我会尝试查看它的操作，有一个模糊的想法，然后决定拒绝.我目前不记得为什么要这样做，但这似乎是我遇到权限失败的原因.因此，我正在更新我的问题:有人可以解释与表有关的"CONTROL"权限吗?

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,

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

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屋！