SQL Server : can you limit access to only one table

Question

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.

Hydro

You can give people access to a single column if you so choose, create their login and just grant select permission on your table – Huge

Answer 1

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.

Unavoidable

Answer 2

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

This approach would work with script also of course.

Kumamoto

This is more helpful than the accepted answer. I could not understand why this answer has fewer votes. – Recrudescence

Answer 3

GRANT SELECT ON [SchemaName].[TableName] to [UserName]

Tommyetommyrot

How does this protect the rest of the tables? – Idolist

It doesn't, thats why the answer wasn't marked as accepted, whereas the accepted one does. Not sure if you're asking me this to point out I was wrong for some odd reason months after this answer that I had long forgotten, either ways, hope my comment helps. – Tommyetommyrot

Sorry this question was brought to my attention in chat. When I commented I actually hadn't realized how old the question was. Still, I think it's valid to question aspects of answers in respect to the original question, regardless of its accept status or vote count. Plenty of questions don't have an accepted answer, and this question is still here because answers are meant to help the OP and future readers. Many of whom may assume that any answer on the page is an answer. – Idolist

This might not do exactly what the asker was looking for, but this is just what I was looking for :) – Hansel

Answer 4

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.

Implausible

Answer 5

It is possible and quite easy. The following code works on my SQL 2019:

USE [master]
GO
-- Create test login deny rights on server layer
IF NOT EXISTS (SELECT NULL FROM sys.server_principals WHERE [name] = 'UserRightTest')
  CREATE LOGIN [UserRightTest] WITH PASSWORD=N'abc1234$', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
--DENY VIEW ANY DATABASE TO [UserRightTest]; -- optional, depends on how the login access the table
DENY VIEW SERVER STATE TO [UserRightTest];
GO

-- Grant only permissions to two tables
USE [MyTestDb]
GO
IF NOT EXISTS (SELECT NULL FROM sys.database_principals WHERE [type] = 'S' AND [name] = N'UserRightTest')
  CREATE USER [UserRightTest] FOR LOGIN [UserRightTest] WITH DEFAULT_SCHEMA = [dbo];
GO
GRANT SELECT ON OBJECT::[dbo].[TestParentTable] TO [UserRightTest];
GRANT SELECT,INSERT,UPDATE,DELETE ON OBJECT::[dbo].[TestChildTable] TO [UserRightTest];
GO

Dominant

Answer 6

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.

Phosphorate

Answer 7

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.

Salahi