SQLCI: SET TRUSTWORTHY ON and other DB Properties

Totu

Hi RG,

Recently I have encountered a problem with the Team City SQL build runner.

When I was developing my SQL CLR assembly the validation of the creation script failed because there is no way to tell the SQLCI if the TRUSTWORTHY property should be ON or OFF and my assembly requires EXTERNAL_ACCESS.

My resolution for now is disabling the validation of the creation script.

I am aware that I could be singing the assembly and load the asymmetric key onto the CI server and that should solve the problem but that is a lot of maintenance I don't want to handle at this point as the project is rather small.
It would be a lot easier to let SQLCI know that it needs to set the TRUSTWORTHY property to ON. I am sure other properties would be nice to have as well but this is a prime example.

Cheers,
Balint

david.conlin

Hi Balint -

Unfortunately, we don't deal particularly well with storing database properties in source control yet.

In the meantime, you can create a migration script to update your database with the required properties. You can read about migration scrips here:

http://documentation.red-gate.com/display/SOC3/Introduction+to+Migrations+V2

Since the migration script must be able to run against any database, it should not use the database name directly, but instead get it from the connection, like this:

@DATABASE] SET TRUSTWORTHY ON');

The migration script will be incorporated into the creation script at the start, so it should successfully validate.[/list]