My Case
I have a scripts folder which includes "GRANT VIEW CHANGE TRACKING ON table TO user" and these appear as differences during a compare, although the permission IS granted in the DB.
It appears that table level VIEW CHANGE TRACKING permissions are not being loaded from the DB. As the "File -> Create Scripts Folder..." also returns a definition WITHOUT the permission.
I've tested with 10.2/10.7 & 11.0b using SQL2012 DB.
To reproduce:
Create a table in a DB and give a view change tracking permission:
CREATE TABLE [TEST_TABLE]([Field] [int] PRIMARY KEY)
GRANT VIEW CHANGE TRACKING ON TEST_TABLE TO [public]
Do a compare against that DB using SQL Compare (or run File -> Create Scripts Folder...), and check the definition of the table.
Output:
-- Columns
CREATE TABLE [dbo].[TEST_TABLE]
(
[Field] [int] NOT NULL
)
GO
-- Constraints and Indexes
ALTER TABLE [dbo].[TEST_TABLE] ADD CONSTRAINT [PK__TEST_TAB__4E1440A47810B1C9] PRIMARY KEY CLUSTERED ([Field])
GO
You'll notice the view permission is missing.
Expected output:
-- Columns
CREATE TABLE [dbo].[TEST_TABLE]
(
[Field] [int] NOT NULL
)
GO
-- Constraints and Indexes
ALTER TABLE [dbo].[TEST_TABLE] ADD CONSTRAINT [PK__TEST_TAB__4E1440A47810B1C9] PRIMARY KEY CLUSTERED ([Field])
GO
-- Permissions
GRANT VIEW CHANGE TRACKING ON [dbo].[TEST_TABLE] TO [public]
GO
Developer Info:
The error appears to be only in the SQL select statement issued to retrieve the permissions. It includes the condition:
WHERE sp.major_id>0 AND sp.class = 1 AND sp.type IN ('RF', 'SL', 'UP','AL','CL','RC','VW','TO')\r\nAND so.schema_id IS NOT NULL
This omits view change tracking's "VWCT" permission type.
The switch statement to map these onto a PermissionAction includes recognising "VWCT" as PermissionAction.ViewChangeTracking, so it appears just to be the select from sys.database_permissions
By modifying the select statement's where clause to be:
WHERE sp.major_id>0 AND sp.class = 1 AND sp.type IN ('RF', 'SL', 'UP','AL','CL','RC','VW','TO','VWCT')\r\nAND so.schema_id IS NOT NULL
... the application appears to produce the intended results.
I've hacked my 10.7 RedGate.SQLCompare.UI.exe with this change and will run that until a fix is release
Anyone else who needs this can do the same by replacing:
INNER JOIN sys.objects so WITH (NOLOCK) ON so.object_id=sp.major_id
WHERE sp.major_id>0 AND sp.class = 1 AND sp.type IN ('RF', 'SL', 'UP','AL','CL','RC','VW','TO')
AND so.schema_id IS NOT NULL
with
INNER JOIN sys.objects so WITH (NOLOCK)ON so.object_id=sp.major_id
WHERE sp.major_id>0 AND sp.class=1 AND sp.type IN('RF','SL','UP','AL','CL','RC','VW','TO','VWCT')
AND so.schema_id IS NOT NULL
(Whitespace is removed to keep the lengths equal.)
Thanks for your email.
I have a scripts folder which includes "GRANT VIEW CHANGE TRACKING ON table TO user" and these appear as differences during a compare, although the permission IS granted in the DB.
It appears that table level VIEW CHANGE TRACKING permissions are not being loaded from the DB. As the "File -> Create Scripts Folder..." also returns a definition WITHOUT the permission.
I've tested with 10.2/10.7 & 11.0b using SQL2012 DB.
To reproduce:
Create a table in a DB and give a view change tracking permission: Do a compare against that DB using SQL Compare (or run File -> Create Scripts Folder...), and check the definition of the table.
Output:
You'll notice the view permission is missing.
Expected output:
Developer Info:
The error appears to be only in the SQL select statement issued to retrieve the permissions. It includes the condition:
This omits view change tracking's "VWCT" permission type.
The switch statement to map these onto a PermissionAction includes recognising "VWCT" as PermissionAction.ViewChangeTracking, so it appears just to be the select from sys.database_permissions
By modifying the select statement's where clause to be: ... the application appears to produce the intended results.
I've hacked my 10.7 RedGate.SQLCompare.UI.exe with this change and will run that until a fix is release
Anyone else who needs this can do the same by replacing: with (Whitespace is removed to keep the lengths equal.)