BUG: Incorrect column permissions shown/scripted (10.5&10.4)

• 
  Here is what I get:
  Run this on SQL Server:

  CREATE TABLE ColTest(
  [A] INT,
  [B] INT,
  [C] INT)
  GRANT UPDATE ([C]) ON [dbo].[ColTest] TO [public] 
  ALTER TABLE [ColTest] DROP COLUMN [B]
  

  
  SQL Compare scripts this:

  SET NUMERIC_ROUNDABORT OFF
  GO
  SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
  GO
  IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
  GO
  CREATE TABLE #tmpErrors (Error int)
  GO
  SET XACT_ABORT ON
  GO
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  GO
  BEGIN TRANSACTION
  GO
  PRINT N'Creating [dbo].[ColTest]'
  GO
  CREATE TABLE [dbo].[ColTest]
  (
  [A] [int] NULL,
  [C] [int] NULL
  )
  GO
  IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
  GO
  IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
  GO
  PRINT N'Altering permissions on [dbo].[ColTest]'
  GO
  GRANT UPDATE ON  [dbo].[ColTest] TO [public]
  GO
  IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
  GO
  IF @@TRANCOUNT>0 BEGIN
  PRINT 'The database update succeeded'
  COMMIT TRANSACTION
  END
  ELSE PRINT 'The database update failed'
  GO
  DROP TABLE #tmpErrors
  GO
  

  No mention of columns in the update permissions. Possibly because SQL Compare is doing this cumulatively, because if I deny to public on A I do get a script denying update to A from SQL Compare.
  
  Can you please provide a working script that reproduces the problem?

  Brian Donahue September 30, 2013 04:34

  - 0 +
• 
  tl;dr - Your example ISN'T the same as my example.
  
  Yes, your example exposed another facet of the same core issue.
  
  You've granted on the LAST column (C), and then deleted an earlier one (B).
  Resulting in the GRANT being for the column after C - which doesn't exist... so it gives a table wide GRANT (very, very wrong).
  
  For my original example, the grant is on the SECOND LAST column.
  I've granted on the SECOND LAST column (C), and then deleted an earlier one (B).
  Resulting in the GRANT being scripted for the column after C - which is D (also very wrong).

  ivanjh September 30, 2013 05:55

  - 0 +
• 
  Run THIS on a SQL server:

  CREATE TABLE [dbo].[ColTest]
  (
  [A] [int] NULL,
  [B] [int] NULL,
  [C] [int] NULL,
  [D] [int] NULL,
  [E] [int] NULL,
  [F] [int] NULL,
  [G] [int] NULL,
  [H] [int] NULL
  )
  ALTER TABLE [ColTest] DROP COLUMN [B] 
  GRANT UPDATE ([C]) ON [dbo].[ColTest] TO [public]
  GRANT UPDATE ([E]) ON [dbo].[ColTest] TO [public]
  GRANT UPDATE ([F]) ON [dbo].[ColTest] TO [public]
  

  
  SQL Compare generates this:

  -- Columns
  CREATE TABLE [dbo].[ColTest]
  (
  [A] [int] NULL,
  [C] [int] NULL,
  [D] [int] NULL,
  [E] [int] NULL,
  [F] [int] NULL,
  [G] [int] NULL,
  [H] [int] NULL
  )
  GO
  -- Permissions
  GRANT UPDATE ([D]) ON [dbo].[ColTest] TO [public]
  GRANT UPDATE ([F]) ON [dbo].[ColTest] TO [public]
  GRANT UPDATE ([G]) ON [dbo].[ColTest] TO [public]
  GO
  

  
  Notice the incorrect columns in the grants (DFG when it should have been CEF).
  
  It appears the column names for the grants are being determined using sys.columns.column_id as a positional index into the column list, instead of matching on column_id.

  ivanjh October 01, 2013 02:30

  - 0 +
• 
  Thanks, I have logged a bug (SC-6564).

  Brian Donahue October 01, 2013 05:11

  - 0 +
• 
  This has been reported as fixed in SQL Compare 10.7. Thanks for your patience.

  Brian Donahue April 24, 2014 03:34

  - 0 +

Add comment

Please sign in to leave a comment.