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

Comments

• Options

  Brian Donahue Posts: 6,590 Bronze 1

  September 30, 2013 4:34AM

  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?

  0 · Share on Twitter
• Options

  ivanjh Posts: 12

  September 30, 2013 5:55AM

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

  0 · Share on Twitter
• Options

  ivanjh Posts: 12

  October 1, 2013 2:30AM

  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.

  0 · Share on Twitter
• Options

  Brian Donahue Posts: 6,590 Bronze 1

  October 1, 2013 5:11AM

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

  0 · Share on Twitter
• Options

  Brian Donahue Posts: 6,590 Bronze 1

  April 24, 2014 3:34AM

  This has been reported as fixed in SQL Compare 10.7. Thanks for your patience.

  0 · Share on Twitter