Making A Database Trustworthy

Packages and compresses schema and contents of SQL Server databases

Moderators: JonathanWatts, Chris Auckland, David Atkinson, david connell, Anu Deshpande, Michelle Taylor, james.billings, Marianne

Making A Database Trustworthy

Postby Davy Mitchell » Mon Feb 09, 2009 10:54 am

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
Davy Mitchell
 
Posts: 14
Joined: Wed Oct 15, 2008 2:41 pm
Location: Scotland

Postby David Atkinson » Mon Feb 09, 2009 1:05 pm

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
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby Davy Mitchell » Mon Feb 09, 2009 1:18 pm

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
Davy Mitchell
 
Posts: 14
Joined: Wed Oct 15, 2008 2:41 pm
Location: Scotland

Postby David Atkinson » Mon Feb 09, 2009 1:24 pm

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
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby Davy Mitchell » Mon Feb 09, 2009 3:37 pm

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
Davy Mitchell
 
Posts: 14
Joined: Wed Oct 15, 2008 2:41 pm
Location: Scotland


Return to SQL Packager 6

Who is online

Users browsing this forum: No registered users and 1 guest