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
我有一个SQL Server 2005数据库,我试图使用Windows身份验证作为受限用户帐户访问。我已经将BUILTIN \ Users添加为数据库用户(在我这样做之前,我甚至无法打开数据库)。我的假设是每个人都应该拥有应用于他们的“公共”角色的权限,因此我没有对角色分配做任何事情。在tblFoo下,我可以使用SSMS Properties对话框(Permissions页面)添加“public”,然后设置显式权限。其中包括SELECT的“Grant”。但跑步
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.
作为一个有限的(BUILTIN \ Users)帐户给我一个错误“对象'tblFoo'选择权限被拒绝,数据库'bar',架构'dbo'”。在属性对话框中,有一个“有效权限”按钮,但它显示为灰色。
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.
此外,我尝试创建一个名为“UserTest”的非私有帐户,在服务器级别添加,然后将其映射到“bar”数据库。这让我将UserTest添加到“用户或角色”列表中,这样我就可以为该帐户运行“有效权限”。根本没有列出任何权限 - 这似乎不对。该帐户必须是公开的,公共拨款(以及其他内容)在tblFoo上选择,那么为什么UserTest帐户不显示有效权限?我觉得我在这里有点疯狂。
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.
ASIDE:我知道许多人不喜欢使用“公共”角色来设置权限。这只是我的修补时间;在最终设计中,我确信我们将拥有几个灵活(自定义)数据库角色。我只想弄清楚我所看到的行为,所以请不要“不要那样做!”答案。
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?
更新:显然我知道只有足够的SQL Server对我自己和他人构成危险。在设置权限时(正如我所说,“除其他”之外),我有DENY CONTROL。当我设置此权限时,我想我试着查看它做了什么,有一个模糊的想法,并决定拒绝。我现在无法回想起为什么这似乎要做,但看起来这就是我获得许可失败的原因。所以我正在更新我的问题:任何人都可以解释“CONTROL”权限,因为它与表有关吗?
2 个解决方案
#1
1
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,
您只需拥有SELECT权限。在原始SQL中(请参阅对话框中的“脚本”图标/按钮),它是对公共的GRANT SELECT ON dbo.tblFoo。这是查看数据所需的唯一权限,
In this case, the error message explicitly mentions "deny". "DENY" is a right in itself, so it mentions it,
在这种情况下,错误消息明确提到“拒绝”。 “DENY”本身就是一个权利,所以它提到了,
If you had no rights, you'd get the message (very approximately) "tblFoo does not exist or you do not have rights"
如果你没有权利,你会收到消息(非常近似)“tblFoo不存在或你没有权利”
"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
受让人实际上具有安全性的所有已定义权限
#2
0
Assuming "UserTest" is a domain user account, connect as a member of the sysadmin role and run
假设“UserTest”是域用户帐户,请以sysadmin角色的成员身份进行连接并运行
EXEC MASTER.dbo.xp_logininfo 'Domain\UserTest', 'all'
(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:
这将显示帐户从其继承安全权限的Windows组等,以及访问级别,例如你会期望看到类似的东西:
account name type privilege mapped login name permission path
domain\usertest user user domain\usertest BUILTIN\Users
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.
这有助于解决帐户从哪个帐户继承权限的问题,例如:哪些Windows组是其中一部分具有数据库权限。如果这一切看起来都不错,那么我会按照你自己的建议,而不是搞乱公共角色。
- Create a database role in your database
- Assign explicit permissions for that role
- Create a server login for your user account
- Open the server login, go to the User Mapping section, click on the database and select the database role you created
在数据库中创建数据库角色
为该角色分配显式权限
为您的用户帐户创建服务器登录
打开服务器登录,转到“用户映射”部分,单击数据库并选择您创建的数据库角色
#1
1
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,
您只需拥有SELECT权限。在原始SQL中(请参阅对话框中的“脚本”图标/按钮),它是对公共的GRANT SELECT ON dbo.tblFoo。这是查看数据所需的唯一权限,
In this case, the error message explicitly mentions "deny". "DENY" is a right in itself, so it mentions it,
在这种情况下,错误消息明确提到“拒绝”。 “DENY”本身就是一个权利,所以它提到了,
If you had no rights, you'd get the message (very approximately) "tblFoo does not exist or you do not have rights"
如果你没有权利,你会收到消息(非常近似)“tblFoo不存在或你没有权利”
"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
受让人实际上具有安全性的所有已定义权限
#2
0
Assuming "UserTest" is a domain user account, connect as a member of the sysadmin role and run
假设“UserTest”是域用户帐户,请以sysadmin角色的成员身份进行连接并运行
EXEC MASTER.dbo.xp_logininfo 'Domain\UserTest', 'all'
(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:
这将显示帐户从其继承安全权限的Windows组等,以及访问级别,例如你会期望看到类似的东西:
account name type privilege mapped login name permission path
domain\usertest user user domain\usertest BUILTIN\Users
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.
这有助于解决帐户从哪个帐户继承权限的问题,例如:哪些Windows组是其中一部分具有数据库权限。如果这一切看起来都不错,那么我会按照你自己的建议,而不是搞乱公共角色。
- Create a database role in your database
- Assign explicit permissions for that role
- Create a server login for your user account
- Open the server login, go to the User Mapping section, click on the database and select the database role you created
在数据库中创建数据库角色
为该角色分配显式权限
为您的用户帐户创建服务器登录
打开服务器登录,转到“用户映射”部分,单击数据库并选择您创建的数据库角色