DacFx shows permanent diff on some permissions if there was a grantor specified in the project sources
Nobody has claimed this yet.
- 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
- Deploy project from sources above to a new db
- Run "Schema compare" from sources to the db and back from db to sources, see no diff
- Uncomment
AS dbolines inpermissions.sql - 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:
Diff on type permissions:
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
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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