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.

You can give people access to a single *column* if you so choose, create their login and just grant select permission on your table — Alex K., Mar 20 '12 at 12:52

score 43 · Accepted Answer · edited Mar 05 '19 at 11:27

43

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.

edited Mar 05 '19 at 11:27

David Aleu

3,922
3
27
48

answered Mar 20 '12 at 12:51

Mitch Wheat

295,962
43
465
541

score 18 · Answer 2 · answered Apr 16 '13 at 02:07

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.

This is more helpful than the accepted answer. I could not understand why this answer has fewer votes. — Muzaffer Galata, May 12 '20 at 06:49

score 5 · Answer 3 · answered Mar 20 '12 at 12:51

5

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

answered Mar 20 '12 at 12:51

Ta01

31,040
13
70
99

2

How does this protect the rest of the tables? – Aaron Bertrand Sep 24 '12 at 21:33
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. – Ta01 Sep 24 '12 at 22:53
2

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. – Aaron Bertrand Sep 24 '12 at 22:56
2

This might not do exactly what the asker was looking for, but this is just what I was looking for :) – jahu Feb 19 '15 at 09:36

score 3 · Answer 4 · answered Mar 20 '12 at 12:52

3

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.

answered Mar 20 '12 at 12:52

Cade Roux

88,164
40
182
265

score 2 · Answer 5 · answered Jan 21 '22 at 06:01

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

score 1 · Answer 6 · answered Mar 20 '12 at 12:52

1

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.

answered Mar 20 '12 at 12:52

Brian

2,229
17
24

score -2 · Answer 7 · answered Oct 13 '14 at 18:26

-2

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.

answered Oct 13 '14 at 18:26

Serj Sagan

28,927
17
154
183

SQL Server : can you limit access to only one table

7 Answers7

Linked