SQL Server 2005“公共” 数据库角色似乎不适用？
我有一个 SQL Server 2005 数据库，我尝试使用 Windows 身份验证以受限用户帐户的身份进行访问。 我已将 BUILTIN\Users 添加为数据库用户（在这样做之前，我什至无法打开数据库）。 我的工作假设是每个人都应该拥有应用于他们的“公共”角色的权限，所以我没有对角色分配进行任何操作。 在 tblFoo 下，我可以使用 SSMS 属性对话框（权限页面）添加“public”，然后设置显式权限。 其中包括 SELECT 的“Grant”。 但
SELECT * from tblFoo;
作为受限（BUILTIN\Users）帐户运行会出现错误“选择对象‘tblFoo’、数据库‘bar’、模式‘dbo’的权限被拒绝”。 在属性对话框中，有一个“有效权限”按钮，但它是灰色的。
此外，我尝试创建一个名为“UserTest”的非特权帐户，在服务器级别添加该帐户，然后将其映射到“bar”数据库。这让我将 UserTest 添加到“用户或角色”列表中，这让我为该帐户运行“有效权限”，根本没有列出任何权限 - 这似乎不正确该帐户必须是公开的。授予（除其他外）在 tblFoo 上选择，那么为什么 UserTest 帐户没有显示有效的权限？我觉得我在这里有点疯狂。
另外：我知道很多人不喜欢使用“ public”角色来设置权限。这只是我的修补时间；在最终设计中，我确信我们将有几个灵活的（自定义）数据库角色。我只是想弄清楚我所看到的行为，所以请没有“不要这样做！”答案：
显然，我知道 SQL Server 对我自己和其他人来说是一个危险。 当我设置此权限时，我想我尝试查找它做了什么，有一个模糊的想法，并决定拒绝。 我目前不记得为什么这似乎是要做的事情，但这似乎就是我获得许可失败的原因。 所以我正在更新我的问题：任何人都可以解释“CONTROL”权限，因为它与表有关？
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;
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.
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.
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您只需要拥有 SELECT 权限即可。 在原始 SQL 中（请参阅对话框中的“脚本”图标/按钮），它是
GRANT SELECT ON dbo.tblFoo to public。 这是查看数据所需的唯一权限，在这种情况下，错误消息明确提到“拒绝”。 “DENY”本身就是一项权利，因此它提到了它，
如果您没有权利，您会收到消息（非常近似）“tblFoo 不存在或您没有权利”
提到“DENY CONTROL”此处。 在这种情况下，您拒绝了公共角色的所有权利。
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,
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.
假设“UserTest”是域用户帐户，作为 sysadmin 角色成员连接并运行
（用您的域名替换“Domain”），
这将显示该帐户继承安全权限的 Windows 组等以及级别访问权限，例如您会期望看到类似以下内容：
这将有助于解决帐户从何处继承权限的问题，例如它所属的 Windows 组拥有数据库的权限。 如果这一切看起来都不错，那么我会听从你自己的建议，而不是扰乱公共角色。
数据库
角色
account
用户映射部分，单击
数据库并选择数据库
您创建的角色
Assuming "UserTest" is a domain user account, connect as a member of the sysadmin role and run
(substituting your domain name for "Domain")
this will display the Windows groups etc. that the account is inheriting security permissions from and the level of access, e.g. you would expect to see something like:
This will help troubleshoot where the account is inheriting permissions from, e.g. which Windows groups it is part of that have permissions to the database. If this all looks OK then I would follow your own advice and not mess with the public role.
database
role
account
User Mapping section, click on the
database and select the database
role you created