Making A Database Trustworthy

[Davy Mitchell]

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

[David Atkinson]

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

[Davy Mitchell]

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

[David Atkinson]

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

[Davy Mitchell]

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