Permissions do not appear in the sysprotects table or in the output of the sp_helprotect stored procedure when you grant the permissions to an object in SQL Server 2000 (900260)

WORKAROUND

1. Back up the database.
2. Verify that the database was upgraded from SQL Server 7.0.
3. Determine the objects that are affected in the database by using the following command:

   Use mydb -- Remember to use a user database.
   go
   select * from syspermissions where actadd = 0 and actmod = 0 

4. Enable the allow updates option in the database by using the sp_configure stored procedure. To do this, use the following command:

   sp_configure 'allow updates', 1
   reconfigure with override
   go
   

5. Start a transaction, and then delete records in the syspermissions table where the columns have the following values:

   Column	Value
   actadd	0
   actmod	0

   To do this, use the following SQL statement:

   use mydb -- Change to the user database.
   go
   begin tran
   delete from syspermissions where actadd = 0 and actmod = 0
   go

6. Verify the correct number and objects, and then commit the transaction by using the following SQL statement:

   select * from syspermissions where id = object_id('mytab')

7. Verify that the sysprotects table and the output of the sp_helprotect stored procedure are correct by using the following SQL statement:

   select * from sysprotects where id = object_id('mytab')

   Note You may alternatively run the following SQL statement:

   sp_helprotect 'mytab'

8. Commit the transaction after you confirm that only the correct number of rows were updated by using the following command:

   commit tran
   go

9. Disable the allow updates option in the database by using the sp_configure stored procedure as follows:

   sp_configure 'allow updates', 0
   reconfigure with override
   go