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

Forum for users of SQL Compare schema synchronization utility

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

Postby ivanjh » Wed May 21, 2014 12:56 am

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:
Code: Select all
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:
Code: Select all
-- 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:
Code: Select all
-- 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:
Code: Select all
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:
Code: Select all
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:
Code: Select all
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
Code: Select all
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.)
ivanjh
 
Posts: 7
Joined: Fri Jul 06, 2007 12:16 am

Postby Anu Deshpande » Fri May 23, 2014 1:47 pm

Thanks for your email.

We have logged a support ticket for you and will get back to you on this.
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Anu Deshpande
 
Posts: 812
Joined: Mon Apr 20, 2009 2:53 pm
Location: Cambridge

Re: BUG: Table VIEW CHANGE TRACKING perm from DB (10.2/7&11.

Postby Mike Upton » Mon Sep 14, 2015 6:07 pm

Thanks so much for the work you put into finding and reporting that issue. I'm glad to tell you that we've included your fix in the product, and released it in the latest frequent updates version (11.3.1.56).
Mike Upton

Technical Lead - SQL Source Control | DLM Automation
Redgate
Mike Upton
 
Posts: 221
Joined: Wed May 11, 2011 7:04 am
Location: Redgate


Return to SQL Compare Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests