SQL Server : can you limit access to only one table

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.


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.


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