DacFx doesn't get schema permission differences in comparison.

[Aldebaran91]

• SqlPackage or DacFx Version: 162.4.92
• .NET Framework (Windows-only) or .NET Core: DotNet6 (6.0.417)
• Environment (local platform and source/target platforms): Windows 10

Steps to Reproduce:

1. Create SQL project from a SQL databse.
2. Create a permission on a schema like -> GRANT SELECT ON SCHEMA::[dbo] TO [DBROLE_TEST]
3. Compare the created SQL project with the SQL-Server (any)

Did this occur in prior versions? If not - which version(s) did it work in?
No version works. No permissions differences are detected. But on Visual Studio 2022(17.11.4) it detects only schema permission differences if the schema isn't 'dbo'.

(DacFx/SqlPackage/SSMS/Azure Data Studio)

[Aldebaran91]

Azure Data Studio: 1.48.0

[ssreerama]

Hi @Aldebaran91, granted permissions are the relationship types to the user and they can be dropped along with the user drop. What I understand from the query is In ADS we are not displaying the granted permissions on user syntax, and the same happening on VS22 as well.

Would like to know what are you expecting from the fix, should show the granted permissions on the SC difference UI only, or also when applying the changes, you are expecting to get the grant permissions statements generated?

[ssreerama]

Hi @Aldebaran91 , granted permissions are the relationship types to the user and they can be dropped along with the user drop. What I understand from the query is In ADS we are not displaying the granted permissions on user syntax, and the same happening on VS22 as well.

Would like to know what are you expecting from the fix, should show the granted permissions on the SC difference UI only, or also when applying the changes, you are expecting to get the grant permissions statements generated?

[Aldebaran91]

In my company we created for each database a SQL project. To check if the SQL project is the same on the production we make a schema compare.

It says that everything is equal. But we noticed that it doesn’t show diffs for permissions on dbo level like:

GRANT SELECT ON SCHEMA::dbo TO DBROLE_XY

But if we change the permission on another schema(not dbo) it will display the difference in the schema compare overview in VS22.

Please fix this bug!

[ssreerama]

With the provided "Grant select on schema", I can see them on any schema other than dbo as you mentioned. The reason is we are trying to see the default permission on a default schema, whereas for other schemas we do see the create schema with granting select to the user on it.

Can you please attach the repro of any sample project and your comparing database script. would like to repro the correct scenario before I we fix it. I'm little confused if your scenario is similar to the below permissions that are not being displayed in the compare result. consider the test user authorized to dbo/any other schema.

GRANT ALTER ANY EXTERNAL FILE FORMAT TO test
GRANT ALTER ANY DATABASE SCOPED CONFIGURATION TO test,
Thanks

[Aldebaran91]

1. I used the Northwind Example from: https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/northwind-pubs

2. Created new db role [DB_TEST] with SELECT permissions on 'dbo' schema. Added login TEST which is member of the db role [DB_TEST].

3. Created SQL project from Northwind.

4. Did a schema compare. 0 diffs OK

5. Extended permissions of db role [DB_TEST] with 'EXECUTE' permissions on dbo.

6. Did a schema compare. 0 diffs NOK

7. Extended permissions for login TEST with
   GRANT ALTER ANY EXTERNAL FILE FORMAT TO TEST
   GRANT ALTER ANY DATABASE SCOPED CONFIGURATION TO TEST

8. Repeated schema compare. Still 0 diffs NOK

I used SQL Server 2022 and VS2022 17.11.4

Can you give me feedback if you get the same results?