Red Gate forums :: View topic - BUG: Incorrect column permissions shown/scripted (10.5&10.4)
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

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

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
ivanjh



Joined: 06 Jul 2007
Posts: 7

PostPosted: Thu Sep 26, 2013 6:39 am    Post subject: BUG: Incorrect column permissions shown/scripted (10.5&10.4) Reply with quote

Execute (I used SQL x64 Version: 11.0.3000.0):
CREATE TABLE [ColTest]([A] [int], [B] [int], [C] [int], [D] [int])
GRANT UPDATE ([C]) ON [dbo].[ColTest] TO [public]
ALTER TABLE [ColTest] DROP COLUMN [B]
--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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6666

PostPosted: Mon Sep 30, 2013 9:34 am    Post subject: Reply with quote

Here is what I get:
Run this on SQL Server:
Code:
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:
Code:
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?
Back to top
View user's profile Send private message
ivanjh



Joined: 06 Jul 2007
Posts: 7

PostPosted: Mon Sep 30, 2013 10:55 am    Post subject: Reply with quote

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).
Back to top
View user's profile Send private message
ivanjh



Joined: 06 Jul 2007
Posts: 7

PostPosted: Tue Oct 01, 2013 7:30 am    Post subject: Reply with quote

Run THIS on a SQL server:
Code:

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:
Code:

-- 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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6666

PostPosted: Tue Oct 01, 2013 10:11 am    Post subject: Reply with quote

Thanks, I have logged a bug (SC-6564).
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6666

PostPosted: Thu Apr 24, 2014 8:34 am    Post subject: Reply with quote

This has been reported as fixed in SQL Compare 10.7. Thanks for your patience.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group