Tips: SQL Server Database Ownership for a Documentum Database


Every now and then, I run into an instance where a database for Documentum has changed slightly, preventing an upgrade or even startup. There is nothing wrong with the database itself, just the way the database ownership is configured. There are several reasons why this could happen:

  • The database was restored after a crash
  • The database was moved to a new server
  • A new user was created to own the database

This is all fine and can be done properly. However, sometimes it is not done correctly because people rely too much on Documentum to create and update the security without actually paying attention to what is happening in the background. So here are a few tips in how changes in SQL Server need to be played out.

The Database dbo Account

In SQL Server, every database has a built-in dbo user. This isn’t a real account, but is tied to a SQL Server login, which can differ from database to database. This login can be either Windows Authentication or an internal SQL Server account. For Documentum, the account is an internal SQL Server account.

The dbo user should own every Table and View in the Documentum database. If not, get your local DBA to change the ownership of the objects. I always update the dbo.sysobjects table directly with one query, but I was a DBA a long time ago. There are better, safer ways to do this which differ between SQL 2000 and 2005. Direct updating is also turned off by default on the server and requires a DBA to turn it on. I’m not going to share the command. If you can’t figure out how to do it on your own, then you shouldn’t be the one making the change.

The account that Documentum uses to connect should be the assigned login for the dbo account. The command is simple. To execute it, make sure that the dbo owns everything first and that the user account in question is not currently a user of the database. Then just open a query window while connected to the database in question as a SQL Server System Administrator and run:

EXEC sp_changedbowner ‘<DesiredDatabaseOwnerName>’

There are a few other options for this stored procedure, but that gets the job done.

Changing the Database Login

This is not as simple as it sounds. Most people just assume that you just change the name and update the connection information in the server.ini file on the Content Server. They are two very important steps. However, once you do this, don’t shutdown the system or it won’t start back up. Why? The database login name is an actual user defined in the Content Repository. So you need to rename the user within Documentum FIRST. This can be readily done through the Documentum Administrator in 5.3 and above. You may want to lock everyone out of the system and back things up first.

What if it is too late and the Repository (docbase) won’t start? Get out your SQL gloves. You need to update the user name in dm_users_s for the account. The user also owns, and has modified, objects (dm_sysobjects_s). The user also owns some ACLs (dm_acl_s) and thus the objects that have the acl domain applied to them need to be updated. Don’t forget to check your operator (operator_name) in your server configuration (dm_server_config_s). There are other tables as well, though this should be enough to get the system off the mat.

You can see why changing the database login name may not be the easiest thing to do because fixing it after it has been done incorrectly can be a mess. Change the user password if you want. That is easy. You just have to re-encrypt it on your Content Server and you are done. I’ll let you look in the documentation for how to do that. It is documented clearly in the Content Server Administration Guide. Just look for the Changing an encrypted password section.

One thought on “Tips: SQL Server Database Ownership for a Documentum Database

  1. Raghava Kumar VSS says:

    Hi,
    Presently, we are facing an issue of similar sorts. We have a federated environment, with one governing repository and two sub repositories. When we create/update user/group information in the governing repository, we run the federation update job to replcate in both the sub repositories x and y. Now, the user/group details that have to be updated in the repository ‘x’ aren’t happening. Instead they are getting updated in repository y. when i checked my federation refresh date, ‘y’ is up to date. But X hasn’t got refreshed from past 15 days.

    COuld you help in this issue?

    Thanks,

    Like

Comments are closed.