I feel the upgrade needs a little more checking on permissions for tables when doing upgrades.

Here's the scenario:
You have one SQL server
You make a backup of the Gold db.
You create a new SQL db with new SQL user.
You restore that backup to the new SQL database. This new db will be used by the AC9 test site.

The Gold db had most of the tables set to schema of dbo, but the tables that were added back in Gold R10 or R11 (not sure which) got created with schema set to the sql user, not set to dbo. For example in my case, ac_ProductGroups should have a schema of dbo, but instead it's schema is 'wa_shopuser' which is the SQL user configured in the Gold R12 database.config.


Now you fire up AC9, point the install to the restored db and run the install. The install fails with permission errors on specific tables. Why?

The tables with non-dbo schema were restored with the non-dbo schema still assigned, but that schema isn't valid in the restored db for the newly created separate sql user. The result is the sql user for the AC9 install will not have permissions to all of the tables in the db.

A simple fix (in my opinion) is to run a query in the install Step 1 area. The query checks all tables in the restored db to look for any that are not assigned to dbo schema. Then throw up a list of warnings like "Table ac_ProductGroups not assigned to dbo schema, upgrade may fail without proper schema assignments".

I don't think you need to check read/update/delete perms on every single table in the database. While that would be a complete test, it seems overkill. If the install could just let the user know 'hey, you've got a permissions issue here that's going to impact things down the road'...that should be enough.