title: When to assign db_owner role to database users
body: <p>This is a long question, but please bear with me as I believe it raises important issues about database ownership and access.</p>
  
  <p>I manage and internationally market a "universal" geothermal data management program, written in Delphi, that is a front end to a SQL Server database.  The data in the database is derived from many diverse measurements generated and used by the program users over time periods of 30 years or more - i.e. they "own" the data, and the database is primarily a way to efficiently store and manage the data.</p>
  
  <p>Like all databases, the database structure needs to be modified from time to time, including new tables, and this modification is delivered by the release of a new version of the program.  The program prompts for a database upgrade, which has to be carried out by a dbo user so that all new tables can be accessed by the other program users.  Unfortunately, the program may be used in remote sites and the IT personnel may not be readily available, so that the new version may get installed but the databases are not upgraded.  What has frequently happened in such locations is that a program user will upgrade the databases without appropriate SQL Server permissions, and then the other users cannot access the new tables and the program crashes.</p>
  
  <p>One of the program customers has taken another approach.  They have created a db_owner role for all the databases used by the program and then make all program users members of the db_owner role.  The program has inbuilt permission levels that can restrict the ability to upgrade databases, so normally only one or two users have this permission.  However, with everyone a member of the db_owner role, it doesn't matter who upgrades the database, all tables will be accessible to all program users.</p>
  
  <p>The advantage of this approach include the following:</p>
  
  <ol>
  <li><p>Access permissions can be granted by the group who uses the program, and who has ultimate responsibility for the database.</p></li>
  <li><p>Knowledge and understanding of the program is passed on within the program users group when staff changes, rather than relying on the IT department as the repository of information on "how it works" (and often they do not know).</p></li>
  <li><p>Direct data-mining and back-door data modification is possible to selected user experts.  While the program has extensive datasearch and editing tools, sometimes these are not enough and the users need hands-on access.</p></li>
  <li><p>The program users retain "ownership" of their data.</p></li>
  </ol>
  
  <p>I would appreciate your comments.  I believe that in circumstances such as these, it is important that all the database users are db_owners, and the group of users controls access. Not allowing db_owner roles (a strategy commonly employed by IT departments) fails to recognize the importance of data ownership and data accessibility, and the responsibility of the database users to manage their own data.</p>