Post reply

Encrypted Stored Procedures

  • December 10, 2009 at 7:50 am

    #135817

    I have a set of encrypted stored procedures that delete from the server whenever I reboot or bounce the service. Anyone ever have this happen to them?

    Luckily I have the source to reapply them. Any Ideas on how to resolve this.

    Setup:

    SQL Server 2008 SP1

    MS Server 2008 R2

  • December 10, 2009 at 12:39 pm

    #1090809

    Well if they were in tempDB I could understand that. Are they in a user database?

    The probability of survival is inversely proportional to the angle of arrival.

  • December 10, 2009 at 1:12 pm

    #1090839

    Good Question, It is a User Database.

  • December 10, 2009 at 1:18 pm

    #1090844

    Check the obvious first... see if there is an agent job that runs on start up that drops them.

    The probability of survival is inversely proportional to the angle of arrival.

  • December 10, 2009 at 1:22 pm

    #1090848

    I haven't set any jobs up on the server yet, but I double checked and there are no jobs running.

  • December 10, 2009 at 2:16 pm

    #1090871

    run this query in master and see if any result set comes back:

    select name, OBJECTPROPERTY(id,'execisstartup')

    from sysobjects where type = 'P'

    group by name, id

    having OBJECTPROPERTY(id,'execisstartup') = 1

    The probability of survival is inversely proportional to the angle of arrival.

  • December 10, 2009 at 2:19 pm

    #1090872

    No results returned.

  • December 10, 2009 at 2:27 pm

    #1090873

    dang. Ya got me stumped. Have you looked in the SQL Server logs ?

    The probability of survival is inversely proportional to the angle of arrival.

  • December 10, 2009 at 3:00 pm

    #1090886

    I have looked at the logs in windows and sql server...Nothing. It has me stumped has well.

  • December 10, 2009 at 5:49 pm

    #1090930

    Have you checked the Schema Changes History Report?

    Have you considered writing a database trigger to log the procedure drops?

    Something along the lines of....

    CREATE TRIGGER log_procedure_drops ON DATABASE

    FOR drop_procedure

    AS

    DECLARE @msg NVARCHAR(MAX)

    SELECT @msg=(SELECT *

    FROM sys.sysprocesses

    WHERE SPID=@@SPID

    FOR XML PATH(''))

    RAISERROR(@msg,16,1) WITH LOG;

    You can add in information about the dropped proc , I can't be bothered right now as it's late.... 😀

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • December 10, 2009 at 7:17 pm

    #1090949

    How about replication?

    We had some procs that were once setup by the previous "dba" to be replicated. On server bounces we lost some of them.

    Just a thought.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

    • Viewing 11 posts - 1 through 10 (of 10 total)

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