Dear Joe,

Sorry about the long post, hope you get to the end of it ;-)

I ran into a little problem while setting up mojoportal and made a small change...

My problem:
I ran into a the problem with my isp, the problem happened while setting up
the MSSQL database.
As I'm not the database owner (dbo), so I cannot create tables named
[dbo].mp_Users etc. the "dbo" needed to be replaced with "code@codewise_nl".

My initial solution:
For the initial creation of the database I was unable to use script
1CreateDatabase.sql, but this should be no problem. In 2CreateTables.sql and
3CreateStoredProcedures.sql I needed to remove all "[dbo]." (a quick search
and replace fixed this). Now when firing up mojoportal I had to change the
upgrade scripts, then I noticed the upgrade scripts are not executed
automatically because mojoPortal.Data.MSSQL\dbPortal.cs uses the dbo directly.
After fixing this all seems to be running nicely.

The "patch":
Now I wanted to provide a solution for future versions where I needed this
and ensure there is a workaround for other people with the same problem.
I took the liberty to add a new appsetting to the web.config:
<add key="MSSQLDatabasePrefix" value="dbo" />
(dbo is code@codewise_nl in my case)

Then I modified all the upgrade scripts so that (the missing . is
intentionally so you can remove the dbo. completely if you want to)
[dbo]. got [*MojoDatabasePrefix*]
dbo. got *MojoDatabasePrefix*
e.g.
CREATE PROCEDURE [dbo].[mp_Roles_SelectRolesUserIsNotIn]
changed to
CREATE PROCEDURE [*MojoDatabasePrefix*][mp_Roles_SelectRolesUserIsNotIn]

Then I changed
mojoPortal.Data.MSSQL\dbPortal.cs

I added private static string GetDatabasePrefix(string connectionString)
This function returns the new appsetting (if not present in the web.config
it returns "dbo")

I modified public static bool RunScript(String script, String overrideConnectionString)
this function now uses GetDatabasePrefix() to replace [*MojoDatabasePrefix*]
and *MojoDatabasePrefix* in the script

If you include this patch people with the same problem can install
mojoportal by:
- do a search/replace on 2CreateTables.sql and 3CreateStoredProcedures.sql to
replace [dbo]. with nothing
- setting the new appsetting MSSQLDatabasePrefix in web.config

Impact:
- Upgrade, new upgrade sql files will need to have the [dbo]. and dbo.
replaced by the new notations
- Security, possible sql injection attacks. This is not likely because we get
the injected text from the web.config and RunScript is only used in the upgrade (as far as I can tell)
- Existing users will still get the [dbo]. in the scripts (because that is the default)

I hope you can use the modified version or give me an other workaround.

You can get the modifications (against 2.2.2.8) from test.onshuis.tk/Mojo-dbo-patch-2.2.8.zip. 

Regards,
Jelle Hissink

Joe,

in the update scripts you should not only replace [dbo]. but also some of the dbo. referals (although dbo. is also used to reference the system tables wich should not be replaced).

Regards,

Jelle