SQL Server:你能限制对一张表的访问吗 [英] SQL Server : can you limit access to only one table

问题描述

我认为答案是否定的，但我希望让某人访问 SQL Server 数据库，但我真的只希望他们能够访问一个表.

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.

限制某人只能访问一个数据库很容易，但不知道我是否可以限制为单个表.

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.

推荐答案

是.

exec sp_msforeachtable "DENY SELECT ON ? TO [username];"
GO

GRANT SELECT ON [schemaName].[tableName] to [username]
Go 

虽然可行，但您最好使用角色 和 AD 组.

While that works, you would probably be better off managing permissions using roles and AD groups.

这篇关于SQL Server:你能限制对一张表的访问吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！