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

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

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

Postby ivanjh » Wed May 21, 2014 1: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 1:16 am

Postby Anu Deshpande » Fri May 23, 2014 2: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: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests