We supply specialised data management software to the geothermal industry. From time to time, the database structure needs to be changed, and we issue a new version of the software that automatically updates the database structure as each database is opened. In most companies, IT personnel install the new version, but are generally unaware of the need to run the program, select and open each database and run the update procedure (this seems to be outside their job description). Consequently, when the users start the program, the database is not updated, and they do not have rights to do so.
The obvious "solution" is to make one user a member of a db_owner role for the specified databases, and give them rights to update the databases (set inside our program). However, this does not work, as any new tables are "owned" by that user and are not visible to the other users. So, the db_owner role is NOT equivalent to dbo for creating new tables.
Some companies have assigned all users to the db_owner role for the specified databases. This works fine, but many IT personnel take exception to allowing all users such high-level permissions.
So, my question is - is it possible to grant true dbo-type permissions to specified databases so that new tables are readily available to all users of the program. As far as I can see, the inability of SQL Server to provide a true dbo role but just to limited databases is a severe shortcoming of the program, especially for database applications where the data is "owned" by the users.