Making A Database Trustworthy

Davy MitchellDavy Mitchell Posts: 19
edited February 9, 2009 9:37AM in SQL Packager Previous Versions
Hi Folks,

One database I am packaging includes Managed Stored Procedures which requires the Database to be Trustworthy during creation. This means a post install sql script can't be used for this.

I have a workaround - generate a C# Project and modify the code to include the one line of SQL required - which works. However I was just wondering if there is something I am missing or a better way of doing this :)

Thanks,
Davy Mitchell

Comments

  • Hi Davy,

    Have you considered generating the script, amending it in Management Studio to add the appropriate line and then packaging this amended script? This may be simpler than generating the C# project.

    David Atkinson
    Red Gate Software
    David Atkinson
    Product Manager
    Redgate Software
  • Hi David,

    Thanks for the suggestion.

    I should have mentioned in my post that I want this to be entirely automated for our build process.

    Will a future version of SQL Packager support this via a 'Managed Store Procedure' option?

    Cheers,
    Davy Mitchell
  • Ah... makes sense.

    Whereabouts in your script do you need to add this line? I guess that a possibility is to write a short script using Perl or a similar language to insert the line in the appropriate script position before packaging it. This should be achievable using the command line, although you'll have to do the inserting bit yourself.

    With regards to a 'Manage Stored Procedure' option, could you tell me precisely what the behaviour of this should be? What would it insert and where?

    Thanks,

    David
    David Atkinson
    Product Manager
    Redgate Software
  • Hi Again,

    The plan is to update the generated PackageExecutor.cs file (via script) to have the command:

    sqlCommand.CommandText = "ALTER DATABASE " + m_DatabaseName + " SET TRUSTWORTHY ON";
    sqlCommand.ExecuteNonQuery();

    Just before the following line:
    string [] dbOptions = m_DatabaseProperties.DatabaseOptions;

    The Managed Stored Procedure option would simply enable/disable the code above 2 lines in the generated code. SET TRUSTWORTHY ON probably has other security implications so default to OFF would make sense.

    Cheers,
    Davy Mitchell
Sign In or Register to comment.