User has been denied select to table, but has access to table via synonym

Question

User has been denied select to table T1 in DB1, but user can create synonym in DB2 and can select from DB1.TB1. I can not deny create synonyms to user. Is it possible to prevent user to select after create synonyms for DB1.TB1?

use d2
create synonym dbo.cl for d1.dbo.cl
select top 100 * from dbo.cl
(100 rows affected)

select top 100 * from d1.dbo.cl
The SELECT permission was denied on the object 'cl', database 'd1', schema 'dbo'.

User has db_owner through AD group in d2

Answer 1

This is because of the "ownership chaining" feature in SQL Server. It means that if one object (in your case a synonym) is calling another object (in your case a table), then it checks if the owner of the objects is the same. If the owner of both objects is the same then the access on the called objects (so your table) is not checked.

This feature is why for example users can have access to a view without having access to the underlying tables.

Normally this ownership chaining is not enabled cross-database by default. So check if it is in your case. It can be granted on database level:

SELECT is_db_chaining_on, name FROM sys.databases;

Or it can be granted on server level for all databases:

EXECUTE sp_configure 'show advanced', 1;  
RECONFIGURE;  
EXECUTE sp_configure 'cross db ownership chaining';