SQL Server : can you limit access to only one table

sql-server permissions sql-server-2016 grant
Andrew Newland picture Andrew Newland · Mar 20, 2012 · Viewed 58.2k times · Source

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.

Answer

Mitch Wheat picture Mitch Wheat · Mar 20, 2012

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.

Related questions

GRANT EXECUTE to all stored procedures

Does the following command effectively give the user, "MyUser," permission to execute ALL stored procedures in the database? GRANT EXECUTE TO [MyDomain\MyUser]

Read More
How do I change the owner of a SQL Server database?

When I accidentally click on the Database Diagrams tab, I get one of the following errors: Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of …

Read More
The SELECT permission was denied on the object 'sysobjects', database 'mssqlsystemresource', schema 'sys'

SETUP: SQL Server 2005 & DotNetNuke 05.01.02. This started with me trying to install a DNN Module that had "select * from dbo.sysobjects" in it's SQL scripts. That failed with the following error: The SELECT permission was denied on the object 'sysobjects', …

Read More