Comments
Sort by recent activity
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. / comments
Run THIS on a SQL server:
CREATE TABLE [dbo].[ColTest]
(
[A] [int] NULL,
[B] [int] NULL,
[C] [int] NULL,
[D] [int]...
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). / comments
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).
Result...