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 

虽然可行，但最好使用角色来管理权限 a>和广告组.

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

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