1

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

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';
| improve this answer | |
  • 1
    Adding one should avoid turning on cross-database chaining system option when non-sysadmins have CREATE permissions. The database-level DB_CHAINING option can be turned on selectively where cross-database chaining is required, ideally only in databases without user CREATE permissions. – Dan Guzman Jun 20 at 10:53
  • I created two database in test enviroment. Create login, user, role. Put user to role. Deny select for created role on first db for the table. But if I create synonym and select from synonym I get error "The SELECT permission was denied on the object 'cl', database 'd1', schema 'dbo'." Sitiation is in production user can select from synonym, in test enviroment user can't select. is_db_chaining_on = 0, for these DB's in test and production. It is not clear why it shows data from synonyn on production – Tom Kev Jun 20 at 16:27
  • Is the chaining not enabled on server level in production? – Wouter Jun 20 at 17:08
  • The chaining not enabled neither at production nor at test enviroment – Tom Kev Jun 20 at 17:44
  • 1
    Yes but when the option is set on server level the databases will still show 0. Thats why you should check also EXECUTE sp_configure 'cross db ownership chaining'; – Wouter Jun 20 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.