Red Gate forums :: View topic - Making A Database Trustworthy
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Packager 6
SQL Packager 6 forum

Making A Database Trustworthy

Search in SQL Packager 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
Davy Mitchell



Joined: 15 Oct 2008
Posts: 14
Location: Scotland

PostPosted: Mon Feb 09, 2009 10:54 am    Post subject: Making A Database Trustworthy Reply with quote

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 Smile

Thanks,
Davy Mitchell
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1124
Location: Twitter: @dtabase

PostPosted: Mon Feb 09, 2009 1:05 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Davy Mitchell



Joined: 15 Oct 2008
Posts: 14
Location: Scotland

PostPosted: Mon Feb 09, 2009 1:18 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1124
Location: Twitter: @dtabase

PostPosted: Mon Feb 09, 2009 1:24 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Davy Mitchell



Joined: 15 Oct 2008
Posts: 14
Location: Scotland

PostPosted: Mon Feb 09, 2009 3:37 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group