BUG: Table VIEW CHANGE TRACKING perm from DB (10.2/7&11.0b)

ivanjhivanjh Posts: 12
edited September 14, 2015 2:07PM in SQL Compare Previous Versions
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.)
· Share on Twitter

Comments

Sign In or Register to comment.