0

This is somewhat of an X/Y Problem: my original problem is that I want to use SSDT to maintain my database design, with the catch that my database design uses two schemas:

  • The dbo schema that contains tables and views that are defined in my SSDT project.
    • All of these tables, views and procedures are defined in the .sqlproj.
  • Another schema, dynamic, which contains tables and views that are managed by the application code itself and should not be managed by SSDT.
    • None of these objects, like CREATE TABLE dynamic.DataForMay2021, exists in the .sqlproj.

The problem is that SSDT's Schema Compare feature will always compare the entire SQL Server database to the .sqlproj SSDT project - which means it will look for the dynamic.DataForMay2021 table in the .sqlproj (which doesn't exist) so it generates a DROP TABLE dynamic.DataForMay2021 (which I don't want), which means I have to manually Exclude all objects in the Schema Comparison window before generating the migration script.

I reasoned that a way around this shortcoming is to run the SSDT Schema Comparison under a different LOGIN/USER that only has access to the dbo schema and not the dynamic schema.

I was able to create a new LOGIN and USER, however I haven't been able to successfully hide the dynamic schema from that user or login, despite trying these steps:

-- Connected to `master` using my Azure DBA:
CREATE LOGIN [SsdtSchemaCompareLogin] WITH PASSWORD=N'*****';

-- Disconnect and reconnect to my database:
CREATE USER [SsdtSchemaCompareUser] FOR LOGIN [SsdtSchemaCompareLogin] WITH DEFAULT_SCHEMA=[dbo]

-- Prevent `SsdtSchemaCompareUser` from seeing any of the `dynamic` schema objects:

DENY SELECT          ON SCHEMA::[dynamic] TO SsdtSchemaCompareUser;
DENY VIEW DEFINITION ON SCHEMA::[dynamic] TO SsdtSchemaCompareUser;

However if I create a new SSMS session as SsdtSchemaCompareLogin I am still able to see all of the objects in the dynamic schema through the following queries:

SELECT * FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_SCHEMA, TABLE_NAME;
SELECT * FROM INFORMATION_SCHEMA.COLUMNS ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;

...and if I use SSMS's Object Explorer while connected as SsdtSchemaCompareLogin then I'm also able to see of the tables in the dynamic schema.

SSDT's Schema Comparison feature itself complains with these errors:

[Warning] The target login is not a member of the 'loginmanager' role. The comparison will be restricted to database scoped elements.
[Error] The reverse engineering operation cannot continue because you do not have View Definition permission on the 'MyDatabase' database.


So how can I prevent SsdtSchemaCompareUser from enumerating the contents of the dynamic schema?

5
  • For your original issue have you looked at github.com/GoEddie/DeploymentContributorFilterer? May 24 at 6:38
  • @MartinSmith Thank you for the suggestion, however that project doesn't seem to relate to SSDT's Schema Compare feature.
    – Dai
    May 24 at 7:51
  • Ah I thought you could use it for that too but maybe not... docs.microsoft.com/en-us/sql/ssdt/… May 24 at 7:53
  • But back to your original plan you can't see object definitions by default in SQL Server so the permissions must be being granted in someway. Does public role have any permissions granted to it? May 24 at 8:01
  • 1
    @MartinSmith I found out that SsdtSchemaCompareUser was a member of db_owner - after removing the user from the ROLE then the objects in the dynamic schema are no-longer visible (hurrah!) - but now SSDT complains that SsdtSchemaCompareUser has any DENY permissions at all (wat?!) - is it possible to hide the fact the user is denied permissions on specific objects from that user? i.e. is it possible to both DENY VIEW DEFINITION ON SCHEMA::[dynamic] and to deny knowing that the dynamic schema exists at all, so it won't complain?
    – Dai
    May 24 at 8:13

Your Answer

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

Browse other questions tagged or ask your own question.