Post reply

Delete Stored Procedure Entry from sys.objects in sql server 2005

  • August 13, 2010 at 1:23 am

    #227735

    Hi ,

    I want to Delete Stored Procedure Entry from sys.objects in sql server 2005.

    SP_CONFIGURE 'ALLOW UPDATES', 1

    GO

    RECONFIGURE WITH OVERRIDE

    GO

    delete from sys.objects where object_id='235147883'

    GO

    This Statement give error message

    Msg 259, Level 16, State 1, Line 2

    Ad hoc updates to system catalogs are not allowed.

    Plz. Help

    Thanks in Advance

  • August 13, 2010 at 2:47 am

    #1206471

    you can directly drop the stored proc using 'Drop proc procedurename' statement. Then the stored proc entry will be automatically be removed in sys.objects. It is not recommended to delete entries in system catalog. Consider it as a last option.

    Thanks,

    Amit Kulkarni

  • August 13, 2010 at 2:52 am

    #1206473

    Hi ,

    Thanks for reply.

    I just want to delete entry from sys.objects table in sql server 205 or if sql server 205 not allow delete on sys.objects table ,can i update the entry in sys.objects ?

    Thanks

  • August 13, 2010 at 4:27 am

    #1206490

    You cannot change the system tables on SQL 2005+ You shouldn't event be considering changing the system tables unless you are happy with the possibility of having a corrupt and unusable database afterwards. The system tables should not be messed with.

    In addition, sys.objects is not a table. It's a view. The system tables aren't even visible.

    Why do you want to delete a row from the table? Why not DROP PROCEDURE?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 13, 2010 at 4:47 am

    #1206499

    Hi,

    Thanks for Reply.

    I want to hide update information about stored procedure from the user.

  • August 13, 2010 at 4:52 am

    #1206500

    Deny them any rights on it. If they have no permissions on the procedure, they can't see that it even exists.

    If you had managed to delete from sys.objects, you would have effectively dropped the procedure. No one would be able to edit it or run it and database consistency checks would fail with irreparable errors from that point on.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply