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.

Improve this question
4
  • 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! Commented Apr 12, 2016 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. Commented Apr 14, 2016 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. Commented Apr 14, 2016 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. Commented Apr 14, 2016 at 4:13

1 Answer 1

Reset to default
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
2
  • 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. Commented May 10, 2016 at 21:05
  • Will await your feedback, Commented May 10, 2016 at 23:50

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.