Microsoft KB Archive/900260

= 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 =

Article ID: 900260

Article Last Modified on 6/28/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Service Pack 3

-



Bug #: 357093 (SQL Server 8.0)



SYMPTOMS
Under certain circumstances, when you grant permissions to an object in Microsoft SQL Server 2000, the permissions that you granted do not appear in the sysprotects virtual table for that database or in the output that you obtain by running the sp_helprotect stored procedure.

Note This issue may occur for databases that are upgraded or are restored from SQL Server 7.0.



CAUSE
This issue occurs when SQL Server 2000 encounters a record with the following permissions set in the syspermissions table.

In SQL Server 7.0, you may run the grant command on a trigger. For example, you may run the following command: Grant all on trig_name to Public This command adds an entry to the syspermissions table with column actadd = 0 and column actmod = 0. By design, you cannot run the same grant command in SQL Server 2000.



WORKAROUND
To work around this issue, follow these steps.

Note We strongly recommend that you do not modify system tables. Additionally, if you must change any system tables, you must back up the database before you make the any changes.  Back up the database. Verify that the database was upgraded from SQL Server 7.0.  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   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   Start a transaction, and then delete records in the syspermissions table where the columns have the following values:

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   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') </li>  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' </li>  Commit the transaction after you confirm that only the correct number of rows were updated by using the following command: commit tran go </li>  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 </li></ol>

Keywords: kbdatabase kbprb kbsql KB900260

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.