Ignore grant only difference in schema compare

Question

I have a project in ssdt and tfs. When I perform a schema compare, I get differences which include objects in which the only difference is a GRANT in the source which does not exist in the imported local schema.

I cannot find an option in schema compare options which will force these to be ignored.

Thanks for any guidance.

Propitious

Answer 1

You need to disable "Permissions" in the Schema Compare Options as follows:

1. Click the black gear icon at the top of the schema compare file. (See picture)
2. On the Object Types tab find Application-scoped > Permissions and uncheck it.

Dirichlet

Is there any way to exclude specific permissions? I have a role in the lower environment that has more permission than I want to grant in the upper environments. I don't want to exclude all permissions though (other roles have permissions that need to be granted in the upper environments). – Mervin

@Mervin I think the best way to handle that using VS/SSDT is with Schema Comparisons. You can create a comparison for each environment and selectively include/exclude the appropriate permissions for each environment. – Dirichlet

I'm using a schema comparison. The problem is, there is always a "GRANT ALTER" and "GRANT CONTROL" statement for a specific role in the script output that I don't want and I don't see any option to uncheck it in the comparison. – Mervin

@Mervin There are times when certain objects get included in the output even when not explicitly selected. It's frustrating and I've never been able to figure out how to handle it myself. – Dirichlet

I assume there's no "post generate script" event that is available for modifying the output from schema compare. – Mervin

@Mervin Unfortunately no. – Dirichlet