Search code examples
sql-serverpermissionssql-server-2016sql-grant

SQL Server : can you limit access to only one table

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.

Solution

  • 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.