Options

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.)

Comments

  • Options
    Anu DAnu D Posts: 876 Silver 3
    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
  • Options
    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).
    Development Lead
    Redgate Software
Sign In or Register to comment.