I had a fun one this week.

I installed a system in a US Pharma Co, who were a tad uptight about their database security. It's a single user app, running against MSSQL Express. By default, since DFCONNID.INI exists, I've just let my app have the sa login, because there are no user accessible ways to do dirt on the database. This was "Not Cool" with the local IT crowd. so they took away my SA account access and gave me db_owner rights in my admin user group, and dbreader/dbwriter for the user group. This worked for the running app.

The fun starts when you run a DF program that notices that you have to add a new table. if a "regular" user tries to run the update it just fails, because you don't have the rights to create tables, with only dbreader and dbwriter. If a dbadmin user runs the script, but the tables are created in the users personal schema. Oops. Half of the system can't find the tables, and the regular users keep trying to re-run the update process, because the table is not visible in their schema.

Upshot of all of this is,

1. Make sure your update processes explicitly state the schema to be updated.
2. If you're being locked down, the admin user that can run updates needs to be a member of db_Owner, and have a properly defined default schema.