BUG: sp_helprotect Output Incorrect for Some Columns

• Microsoft SQL Server version 4.2 for OS/2

SYMPTOMS

When "sp_helprotect <table>" is issued on a table in Microsoft SQL Server version 4.2 for OS/2, the output contains duplicate rows for every column in the table.

CAUSE

When granting and/or revoking permissions on individual columns of a table, sp_helprotect might generate duplicate rows for every column in the table which makes it hard to see which permissions you do or do not have.

The following example illustrates this:

   CREATE TABLE test (
   col1 int, col2 int, col3 int, col4 int, col5 int,
   col6 int, col7 int, col8 int, col9 int, col10 int)
   go

   GRANT SELECT ON test TO public
   go
   GRANT UPDATE ON test TO public
   go
   REVOKE UPDATE ON test (col5) FROM public
   go

   sp_helprotect test
   go

       Type    Action      User       Column
       ----    ------      ----       ------

       Grant   Select      public     All
       Grant   Update      public     col1
       Grant   Update      public     col1
       Grant   Update      public     col10
       Grant   Update      public     col10
       Grant   Update      public     col2
       Grant   Update      public     col2
       Grant   Update      public     col3
       Grant   Update      public     col3
       Grant   Update      public     col4
       Grant   Update      public     col4
       Grant   Update      public     col5
       Grant   Update      public     col6
       Grant   Update      public     col6
       Grant   Update      public     col7
       Grant   Update      public     col7
       Grant   Update      public     col8
       Grant   Update      public     col8
       Grant   Update      public     col9
       Grant   Update      public     col9
       Revoke  Update      public     col5

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2 for OS/2. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.