SQL Compare Bug ? - DB vs VS DB Project - Object Permissions Ignored

howarthcd

I seem to have encountered a possible bug in SQL Compare 14.5.22.19589 Professional whereby permissions are ignored from the comparison when comparing a SQL 2019 DB with a VS DB Project of the same DB.

This occurs even when the 'Redgate defaults' project options are used.

Steps to reproduce:

1. Create the DB & schema:

CREATE DATABASE SCTest

GO

USE SCTest

GO

CREATE ROLE MyRole

GO

CREATE PROCEDURE dbo.MyProc

AS

SELECT 1

GO

GRANT EXECUTE ON dbo.MyProc TO MyRole

GO

2. Use SQL Compare to generate a scripts folder.
3. Compare the DB with the scripts folder using SQL Compare with 'Redgate defaults' - verify no differences detected.
4. Execute the following against the DB to create a difference : 

REVOKE EXECUTE ON dbo.MyProc TO MyRole

GO
5. Refresh the comparison and check that the difference is detected.
6. Execute the following against the DB to revert to the original state: 

GRANT EXECUTE ON dbo.MyProc TO MyRole

GO
7. Use VS to script the DB to a database project, use default options but select the option to script permissions (version of VS and the DB project extension doesn't seem to matter for the purpose of the repro).

8. Edit the SQL Compare project and set the target as the VS database project folder.
9. Refresh the comparison - verify that no differences are detected, but notice that the permission grant statement is absent from the 'SQL view' when dbo.MyProc is selected (refer to attached image).
10. Execute the following against the DB : 

REVOKE EXECUTE ON dbo.MyProc TO MyRole

GO
11. Refresh the comparison and note that no differences are detected - expected behaviour is that the change made to the DB in step 10 is identified by SQL Compare.


To me this looks like a bug, but please could you confirm and advise if a fix will be made available?

Thanks
Chris

Image shows that even with the EXECUTE permission assigned in the DB and the GRANT EXECUTE statement included in the VS DB project that the permission assignment isn't visible in SQL Compare:

Eddie D

Hi, thank you for your forum post.

I am unable to replicate the reported problem using VS 2017, SQL 2019 database and SQL Compare V14.6.0.19635.  See the screen shot below:



I am a complete novice in regards to Visual Studio, so I may have taken different steps to yourself in creating the VS project.

However, a colleague has been able to reproduce this error and a bug report has been submitted.  The Bug report reference is SC-10948.  I will update this post once we have further information on a possible bug fix.  

Many Thanks
Eddie