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

ivanjhivanjh Posts: 12
Execute (I used SQL x64 Version: 11.0.3000.0):
CREATE TABLE [ColTest]([A] [int], [int], [C] [int], [D] [int])
GRANT UPDATE ([C]) ON [dbo].[ColTest] TO [public]
ALTER TABLE [ColTest] DROP COLUMN
--Run export/compare
--Shows: GRANT UPDATE ([D]) ON [dbo].[ColTest] TO [public]
--Expected: GRANT UPDATE ([C]) ON [dbo].[ColTest] TO [public]

When using compare - it'll continually show a difference that doesn't exist.
When creating a scripts folder - it'll script out the wrong permissions (doh!)

I'm guessing there's an assumption that sys.columns.column_id is always sequential. Nope, deletes leave holes.

Actively causing some confusion here... a fix would be nice.

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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?
  • 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).
  • 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.
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Thanks, I have logged a bug (SC-6564).
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    This has been reported as fixed in SQL Compare 10.7. Thanks for your patience.
Sign In or Register to comment.