Grant Select To Synonym For Specified Columns

I’ve been trying to restrict a user’s select access to a synonym. Consider the following below:

CREATE USER Test_User WITHOUT LOGIN;
CREATE SYNONYM S_TEST FOR dbo.Items;

GRANT SELECT ON dbo.S_TEST (Id, [Name]) TO Test_User;

I get an error:

Msg 1020, Level 16, State 3, Line 4
Sub-entity lists (such as column or security expressions) cannot be specified for entity-level permissions.

But if I change it to use dbo.Items directly then it works:

GRANT SELECT ON dbo.Items (Id, [Name]) TO Test_User;

How can I restrict the SELECT on a synonym for a specified user? Is this possible? If no, are there any alternatives?

Answer

“How can I restrict the SELECT on a synonym for a specified user? “ You can’t. A Synonym is just an alternative name for an object. The check to see if the object even exists is deferred till runtime when referencing the SYNONYM not when it’s created. For example CREATE SYNONYM dbo.MySynonym FOR MadeUp.TableObject; will create the synonym even though the reference object doesn’t exist.

CREATE SYNONYM dbo.MySynonym FOR MadeUp.TableObject;
GO

SELECT *
FROM dbo.MySynonym; --fails
GO

DROP SYNONYM dbo.MySynonym;
GO

All the permissions need to be set up the object that the Synonym references, not the synonym itself.