I think the answer is no but I'm looking to give someone access to a SQL Server database but I only really want them to have access to one table.
我认为答案是肯定的，但我想让某人访问SQL Server数据库，但我真的希望他们可以访问一个表。
It's easy enough to limit someone to only access one database but have no idea if I can limit to a single table.
很容易限制某人只访问一个数据库，但不知道我是否可以限制到一个表。
My thoughts were to create another database with a synonym to the other table and then limit the access to that database but I wondered if someone could think of a better way.
我的想法是用另一个表的同义词创建另一个数据库，然后限制对该数据库的访问，但我想知道是否有人能想到更好的方法。
I'm also not convinced that it will work as I think there will be a conflict of permissions.
我也不相信它会起作用，因为我认为会有权限冲突。
7 个解决方案
#1
22
Yes.
是。
exec sp_msforeachtable "DENY SELECT ON '?' TO [username];"
GO
GRANT SELECT ON [schemaName].[tableName] to [username]
Go
While that works, you would probably be better off managing permissions using roles and AD groups.
虽然这有效，但您可能最好使用角色和AD组来管理权限。
#2
5
The problem with looping through all tables and denying access would be if you add a new table.
循环遍历所有表并拒绝访问的问题是，如果添加新表。
The important thing is to not give the user 'db_datareader' access to the whole database. Using the UI you can use the User Mapping tab under the login, you can create the user with 'public' access only. Then you can go to the database and grant that user SELECT access to the particular table (by clicking the oddly named "Search" button under Securables tab).
重要的是不要让用户'db_datareader'访问整个数据库。使用UI可以使用登录下的“用户映射”选项卡，您可以创建仅具有“公共”访问权限的用户。然后，您可以转到数据库并授予该用户对特定表的SELECT访问权限（通过单击Securables选项卡下奇怪命名的“搜索”按钮）。
This approach would work with script also of course.
当然，这种方法也适用于脚本。
#3
3
GRANT SELECT ON [SchemaName].[TableName] to [UserName]
#4
2
Certainly. GRANT the permissions you want.
当然。授予您想要的权限。
When you give a user access to a database, look at the roles they are assigned and what rights those roles have.
当您授予用户访问数据库的权限时，请查看他们分配的角色以及这些角色拥有的权限。
The problem is that people generally grant too broad permissions in the beginning.
问题是人们通常在开始时授予过多的权限。
#5
0
Sure you can. After creating the user and giving them access to the database, grant only select access (or whatever level they need) to that table.
你当然可以。在创建用户并授予他们访问数据库权限后，只授予该表的select访问权限（或他们需要的任何级别）。
#6
-1
A better approach would be to create a separate schema, create a proc in that schema. Then allow the user to EXEC that proc. That's it. You could create a view in that schema and that may be more of what you're after.
更好的方法是创建一个单独的模式，在该模式中创建一个proc。然后允许用户执行proc。而已。您可以在该架构中创建一个视图，这可能更像是您所追求的。
#7
-1
The better way is creating securable for that specific table. IT will ask you what are you going to secured; table, view, database. Then you choose the specific table to secure and exclude that user from that table.
更好的方法是为特定表创建安全性。 IT部门会问你要保护什么;表，视图，数据库。然后，您选择要保护的特定表，并从该表中排除该用户。
#1
22
Yes.
是。
exec sp_msforeachtable "DENY SELECT ON '?' TO [username];"
GO
GRANT SELECT ON [schemaName].[tableName] to [username]
Go
While that works, you would probably be better off managing permissions using roles and AD groups.
虽然这有效，但您可能最好使用角色和AD组来管理权限。
#2
5
The problem with looping through all tables and denying access would be if you add a new table.
循环遍历所有表并拒绝访问的问题是，如果添加新表。
The important thing is to not give the user 'db_datareader' access to the whole database. Using the UI you can use the User Mapping tab under the login, you can create the user with 'public' access only. Then you can go to the database and grant that user SELECT access to the particular table (by clicking the oddly named "Search" button under Securables tab).
重要的是不要让用户'db_datareader'访问整个数据库。使用UI可以使用登录下的“用户映射”选项卡，您可以创建仅具有“公共”访问权限的用户。然后，您可以转到数据库并授予该用户对特定表的SELECT访问权限（通过单击Securables选项卡下奇怪命名的“搜索”按钮）。
This approach would work with script also of course.
当然，这种方法也适用于脚本。
#3
3
GRANT SELECT ON [SchemaName].[TableName] to [UserName]
#4
2
Certainly. GRANT the permissions you want.
当然。授予您想要的权限。
When you give a user access to a database, look at the roles they are assigned and what rights those roles have.
当您授予用户访问数据库的权限时，请查看他们分配的角色以及这些角色拥有的权限。
The problem is that people generally grant too broad permissions in the beginning.
问题是人们通常在开始时授予过多的权限。
#5
0
Sure you can. After creating the user and giving them access to the database, grant only select access (or whatever level they need) to that table.
你当然可以。在创建用户并授予他们访问数据库权限后，只授予该表的select访问权限（或他们需要的任何级别）。
#6
-1
A better approach would be to create a separate schema, create a proc in that schema. Then allow the user to EXEC that proc. That's it. You could create a view in that schema and that may be more of what you're after.
更好的方法是创建一个单独的模式，在该模式中创建一个proc。然后允许用户执行proc。而已。您可以在该架构中创建一个视图，这可能更像是您所追求的。
#7
-1
The better way is creating securable for that specific table. IT will ask you what are you going to secured; table, view, database. Then you choose the specific table to secure and exclude that user from that table.
更好的方法是为特定表创建安全性。 IT部门会问你要保护什么;表，视图，数据库。然后，您选择要保护的特定表，并从该表中排除该用户。