'Does having permissions to SELECT and View Definition on a schema give you special permissions on that schema's database?
My colleague has performed a strange form of magic that has revealed my ignorance. He has made a schema containing views, [VW_COLLEAGUE] and given users the permission to SELECT and View Definition on anything in that schema. On that same database, another schema exists, [SECRET]. Users do not have any permissions on that schema. Yet, when my colleague has done this
CREATE VIEW [VW_COLLEAGUE].[REVEAL_SECRET] AS
SELECT *
FROM [SECRET].[SECRET_TABLE]
the users can query that view just fine despite having no power to query the table. One notable property of his [VW_COLLEAGUE] views is that all of them only reference the database to which [VW_COLLEAGUE] belongs. I tried to repeat his trick with a query that referenced multiple databases, but my users were treated like they did not have permission. This gives me three deeply related questions:
- Why does my colleagues' trick work?
- Is my colleagues' trick intended behavior?
- Why does referencing multiple databases break it?
Note: I'm very open to the possibility that I'm extremely ignorant. I'm notoriously bad with permissions.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
