microsoft / microsoft/DacFx

DacFx shows permanent diff on some permissions if there was a grantor specified in the project sources

Open
#385 6 comments 7 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug
Dominant language
C#
Stars
460
Forks
29
Avg merge
4d 9h
Merged PRs (30d)
7

Description

  • SqlPackage or DacFx Version:
    • SSDT - 16.0.62205.05200
    • SqlPackage - 162.0.52.1
  • .NET Framework (Windows-only) or .NET Core:
  • Environment (local platform and source/target platforms):
    • Windows 10
    • VS 2019

DacFx shows permanent diff on grantor option in permission definition if the permission is on user-defined type or schema. Other permissions (on sp or fn) are not affected by this bug. GRANT <whatever> ON SCHEMA::my_schema AS dbo is a valid syntax and should not produce any diff between project sources and target database.

Steps to Reproduce:

Minimal repro: permissions_diff.zip

  1. Deploy project from sources above to a new db
  2. Run "Schema compare" from sources to the db and back from db to sources, see no diff
  3. Uncomment AS dbo lines in permissions.sql
  4. Run "Schema compare" from sources to db - no diff, back from db to sources - see permanent diff which cannot be fixed by redeployment

Diff on schema permissions:
image

Diff on type permissions:
image

As a workaround we have a rule in our custom linter which prevents devs from defining grantor in permissions on types and schemas.

Did this occur in prior versions? If not - which version(s) did it work in?

If I'm not mistaken then in some of previous versions there was the same bug in forward-comparison from the project to DB but I have no proofs, unable to reproduce this behavior right now.

(DacFx/SqlPackage/SSMS/Azure Data Studio)

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the permissions.sql file in the permissions_diff.zip reproduction and follow the listed schema-compare steps, especially with the AS dbo grantor lines enabled. Investigate how DacFx handles grantors on schema and user-defined type permissions; done means comparing sources to the database and back produces no permanent diff and redeployment does not recreate it.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.