bug in column permission compare
Brian Donahue
Posts: 6,590 Bronze 1
Hi Sean,
Thanks for the info; we're going to take a look at that. This hasn't
been noticed before, but it will probably only affect users of version 3 and
up because version 2 rebuilt tables when dropping or adding a column 99.9%
of the time. If this is affecting you, you could try turning on the option
to preserve column order in SQL Compare which should pretty much work around
the problem.
Regards,
Brian Donahue
Red Gate Technical Support
"Sean" <sbrockway@mailinator.com> wrote in message
news:1XVqF2xUEHA.2140@server53...
> The script below illustrates a bug (rooted in MS) that affects SQL Compare
> table column permissions
> -Run the script (in QA), which creates two DBs.
> -Note that the DBs are identical, except that [bb] had an extra column
> during creation that gets dropped.
> -Compare the DBs in SQL Compare
> -The perms for column c show up different. So sync bb to be like aa.
After
> the refresh, they are still different.
>
> Why you may ask? Check out the column perms in Enterprise Manager. In bb
> the grant permision on column c doesn show up. now run sp_helprotect on
> bb.table1. There it is! Why isn't EM showing it? Cuz they use
> sp_MSObjectprivs. Run that and sure enough the perm is missing (hint 205
=
> grant, 206=deny)
> I suspect SQL compare is either using sp_MSObjectPrivs or a similar query
to
> obtain the column permissions.
>
> Why does sp_MSObjectprivs fail? Apparently when you drop column q in the
> script, the internal column ID for column c remains 4 (because it was the
> 4th column added) and doesn't get decremented to 3 (there is now a gap
where
> q was in the column IDs. the sproc does a check that column ID <= # of
> columns in the table. Since there are only 3 columns, but the ID for
column
> c is 4, it doesn't get shown in EM. The grant priv is still there, but
you
> won't see it in EM, and it won't script out via EM.
> MS is aware of the issue as of this posting. The work around is to drop
and
> recreate the table so the columns IDs are sequential.
> If SQL Compare tweaks it's code to get the privs the same way
sp_helprotect
> does, the bug should disappear for SQL Compare.
>
Script
> CREATE DATABASE [aa]
> GO
> USE [aa]
> GO
> -- Columns
> CREATE TABLE [dbo].[Table1]
> (
> [a] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [c] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> -- Permissions
> GRANT SELECT ([c]) ON [dbo].[Table1] TO [public]
> GO
>
> CREATE DATABASE [bb]
> GO
> USE [bb]
> GO
> -- Columns
> CREATE TABLE [dbo].[Table1]
> (
> [a] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [q] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [c] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> -- Permissions
> GRANT SELECT ([c]) ON [dbo].[Table1] TO [public]
> GO
> ALTER TABLE [dbo].[Table1] DROP COLUMN [q]
> GO
>
> Sean
>
>
Thanks for the info; we're going to take a look at that. This hasn't
been noticed before, but it will probably only affect users of version 3 and
up because version 2 rebuilt tables when dropping or adding a column 99.9%
of the time. If this is affecting you, you could try turning on the option
to preserve column order in SQL Compare which should pretty much work around
the problem.
Regards,
Brian Donahue
Red Gate Technical Support
"Sean" <sbrockway@mailinator.com> wrote in message
news:1XVqF2xUEHA.2140@server53...
> The script below illustrates a bug (rooted in MS) that affects SQL Compare
> table column permissions
> -Run the script (in QA), which creates two DBs.
> -Note that the DBs are identical, except that [bb] had an extra column
> during creation that gets dropped.
> -Compare the DBs in SQL Compare
> -The perms for column c show up different. So sync bb to be like aa.
After
> the refresh, they are still different.
>
> Why you may ask? Check out the column perms in Enterprise Manager. In bb
> the grant permision on column c doesn show up. now run sp_helprotect on
> bb.table1. There it is! Why isn't EM showing it? Cuz they use
> sp_MSObjectprivs. Run that and sure enough the perm is missing (hint 205
=
> grant, 206=deny)
> I suspect SQL compare is either using sp_MSObjectPrivs or a similar query
to
> obtain the column permissions.
>
> Why does sp_MSObjectprivs fail? Apparently when you drop column q in the
> script, the internal column ID for column c remains 4 (because it was the
> 4th column added) and doesn't get decremented to 3 (there is now a gap
where
> q was in the column IDs. the sproc does a check that column ID <= # of
> columns in the table. Since there are only 3 columns, but the ID for
column
> c is 4, it doesn't get shown in EM. The grant priv is still there, but
you
> won't see it in EM, and it won't script out via EM.
> MS is aware of the issue as of this posting. The work around is to drop
and
> recreate the table so the columns IDs are sequential.
> If SQL Compare tweaks it's code to get the privs the same way
sp_helprotect
> does, the bug should disappear for SQL Compare.
>
Script
> CREATE DATABASE [aa]
> GO
> USE [aa]
> GO
> -- Columns
> CREATE TABLE [dbo].[Table1]
> (
> [a] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [c] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> -- Permissions
> GRANT SELECT ([c]) ON [dbo].[Table1] TO [public]
> GO
>
> CREATE DATABASE [bb]
> GO
> USE [bb]
> GO
> -- Columns
> CREATE TABLE [dbo].[Table1]
> (
> [a] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [q] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [c] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> -- Permissions
> GRANT SELECT ([c]) ON [dbo].[Table1] TO [public]
> GO
> ALTER TABLE [dbo].[Table1] DROP COLUMN [q]
> GO
>
> Sean
>
>
This discussion has been closed.
