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:
Hi, thank you for your forum post.
This occurs even when the 'Redgate defaults' project options are used.
Steps to reproduce:
1. Create the DB & schema:
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 :
5. Refresh the comparison and check that the difference is detected.
6. Execute the following against the DB to revert to the original state:
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 :
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