SQL Server : can you limit access to only one tabl

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.

Answer 1

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.