0

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.

  • I don't know SQL Server all that well, but it seems to me that the "obvious" solution is for your installer to do the schema migration! – Michael Hampton Apr 12 '16 at 23:48
  • As explained previously, this is not the "obvious" solution as IT personnel in many companies do not consider running applications to be part of their job description. They simply install new versions. – Errol Anderson Apr 14 '16 at 0:47
  • I said nothing about your existing manual process. I meant that this should be entirely automated and handled by setup.exe, which presumably IT is running. – Michael Hampton Apr 14 '16 at 0:59
  • Thanks - that's an idea worth considering, However, the tricky part is ensuring that the installer, setup.exe, knows of all the databases requiring upgrades. Most users assign a separate database for each geothermal prospect they are collecting data for. Typically this is two or three databases, but it is conceivable that agencies in some countries (e.g. Indonesia) could be managing data for more than 100 geothermal prospects. – Errol Anderson Apr 14 '16 at 4:13
0

Without discussing the security issues of granting non IT admin users admin rights to the database.

To solve below problem:

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.

prefix your new SQL Objects with "dbo" e.g.

CREATE TABLE dbo.NewTable1( ... )
CREATE PROCEDURE dbo.NewProcedure1 ...
| improve this answer | |
  • This seems a good answer, thanks. I'll try this in the next revision of the code, and let you know if this solves the problems. – Errol Anderson May 10 '16 at 21:05
  • Will await your feedback, – Alex May 10 '16 at 23:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.