none
How to view the modified time of extended storeproc on master db

    Question

  • Dear all,

    We're using sql server 2005 and already used the following sql to change the permission of one system extended store proc in production:

    use master
    deny execute on sp_replwritetovarbin to public
    go

    Is there any log event to indicate the time of issueing the above sql?

    Thanks and regards,
    Wallace


    Wednesday, December 25, 2013 2:55 PM

Answers

  • Nothing built in. But you can track it with a DDL trigger or by setting up an event notification.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, December 25, 2013 3:36 PM
  • but the modified time column is same as create time column and cannot reflect the actual modified time


    It's because you haven't modified the system SP, only the permissions for it, that don't changes the SP itself.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Chan Wai Yin Friday, December 27, 2013 1:11 PM
    Friday, December 27, 2013 9:51 AM
    Moderator

All replies

  • Nothing built in. But you can track it with a DDL trigger or by setting up an event notification.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, December 25, 2013 3:36 PM
  • check from below cmd

    select * from sys.all_objects where type='p'


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Thursday, December 26, 2013 1:26 AM
  • check from below cmd

    select *from sys.all_objectswhere type='p'

    1) Maybe you should read the question?

    2) The object type for extended stored procedures is X.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, December 26, 2013 9:34 AM
  • check from below cmd

    select * from sys.all_objects where type='p'


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    I have select from sys.allobjects but the modified time column is same as create time column and cannot reflect the actual modified time

    Friday, December 27, 2013 3:26 AM
  • but the modified time column is same as create time column and cannot reflect the actual modified time


    It's because you haven't modified the system SP, only the permissions for it, that don't changes the SP itself.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Chan Wai Yin Friday, December 27, 2013 1:11 PM
    Friday, December 27, 2013 9:51 AM
    Moderator