This page last changed on Oct 27, 2006 by ivan@atlassian.com.
Important

It is not currently possible to change a user's username through the user interface. This page describes modifications that you can do if you have direct access to the database.

We recommend you perform a backup before you proceed with any changes below.

Both these solutions only work if you are using the built-in Confluence user management.

It is not currently possible to update a user's username through the Confluence user interface. There is a Jira issue where you can vote for this feature: CONF-4063.

The methods below involve directly modifying the database, they should not be performed without doing a backup first. Usernames are used as the unique identifiers for all actions of a user throughout the system. Therefore, you must update all the tables that use this to reference the user.

This information has been adapted from the forum thread.

Solution 1: Update the database directly

Assuming you have a mapping table called usermigration that contains columns for old and new names, the following SQL will work for Confluence.

update attachments
set creator = newusername from usermigration u
where creator = u.oldusername;

update attachments
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update content
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update content
set creator = newusername from usermigration u
where creator = u.oldusername;

update content
set username = newusername from usermigration u
where username = u.oldusername;

update extrnlnks
set creator = newusername from usermigration u
where creator = u.oldusername;

update extrnlnks
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update links
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update links
set creator = newusername from usermigration u
where creator = u.oldusername;

update notifications
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update notifications
set creator = newusername from usermigration u
where creator = u.oldusername;

update pagetemplates
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update pagetemplates
set creator = newusername from usermigration u
where creator = u.oldusername;

update spaces
set creator = newusername from usermigration u
where creator = u.oldusername;

update spaces
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update spacepermissions
set permusername = newusername from usermigration u
where permusername = u.oldusername;

update spacepermissions
set creator = newusername from usermigration u
where creator = u.oldusername;

update spacepermissions
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update contentlock
set creator = newusername from usermigration u
where creator = u.oldusername;

update contentlock
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update emailtemplates
set creator = newusername from usermigration u
where creator = u.oldusername;

update emailtemplates
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update emailtemplates
set username = newusername from usermigration u
where username = u.oldusername;

update os_user
set username = newusername from usermigration u
where username = u.oldusername;

update trackbacklinks
set creator = newusername from usermigration u
where creator = u.oldusername;

update trackbacklinks
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

Solution 2: Dump database to SQL, update then reimport

If your database supports it (e.g. MySQL), perform the following steps.

Assuming existing confluence user 'olduser' exists.

1. Create new user 'newuser' via Confluence.
2. Backup confluence database to SQL file (may not be supported – consult your database documentation).
3. Find all occurences of 'olduser' and replace with 'newuser' via favourite text editor, EXCEPT FOR THE SINGLE INSTANCE WITHIN THE OS_USER TABLE.
4. Restore SQL file to Confluence database.

Log in as 'newuser'. All content previously associated with 'olduser' should now belong to 'newuser'. Thus, you can now safely remove 'olduser' via Confluence.

The reason to not search and replace in the os_user table

Potential corruption of database

Say that a page is marked as having been created for 'userA'. Now you rename that user in os_user table to 'userB'. When you view the page information for the page created by 'userA' it will still be marked as having been created by a user that no longer exists.

RELATED TOPICS
Adding a Group
Adding a New User
Adding or Removing a User from a Group
Changing Usernames
Editing User Details

Not sure how up to date the SQL above is for this problem. Below is the SQL I had to use to fix a naming problem for users as we moved from one LDAP service to another (their 6+2 login names were slightly different in some cases).

begin
for i in (select * from usermigration)
loop

delete from external_entities
where name = i.newusername;

update external_entities
set name = i.newusername
where name = i.oldusername;

update attachments
set creator = i.newusername
where creator = i.oldusername;

update attachments
set lastmodifier = i.newusername
where lastmodifier = i.oldusername;

update content
set lastmodifier = i.newusername
where lastmodifier = i.oldusername;

update content
set creator = i.newusername
where creator = i.oldusername;

update content
set username = i.newusername
where username = i.oldusername;

update content_label
set owner = i.newusername
where owner = i.oldusername;

update draft
set owner = i.newusername
where owner = i.oldusername;

update extrnlnks
set creator = i.newusername
where creator = i.oldusername;

update extrnlnks
set lastmodifier = i.newusername
where lastmodifier = i.oldusername;

update links
set lastmodifier = i.newusername
where lastmodifier = i.oldusername;

update links
set creator = i.newusername
where creator = i.oldusername;

update notifications
set username = i.newusername
where username = i.oldusername;

update notifications
set lastmodifier = i.newusername
where lastmodifier = i.oldusername;

update notifications
set creator = i.newusername
where creator = i.oldusername;

update pagetemplates
set lastmodifier = i.newusername
where lastmodifier = i.oldusername;

update pagetemplates
set creator = i.newusername
where creator = i.oldusername;

update spaces
set creator = i.newusername
where creator = i.oldusername;

update spaces
set lastmodifier = i.newusername
where lastmodifier = i.oldusername;

update spacepermissions
set permusername = i.newusername
where permusername = i.oldusername;

update spacepermissions
set creator = i.newusername
where creator = i.oldusername;

update spacepermissions
set lastmodifier = i.newusername
where lastmodifier = i.oldusername;

update contentlock
set creator = i.newusername
where creator = i.oldusername;

update contentlock
set lastmodifier = i.newusername
where lastmodifier = i.oldusername;

--update emailtemplates
--set creator = i.newusername
--where creator = i.oldusername;

--update emailtemplates
--set lastmodifier = i.newusername
--where lastmodifier = i.oldusername;

--update emailtemplates
--set username = i.newusername
--where username = i.oldusername;

update os_user
set username = i.newusername
where username = i.oldusername;

update trackbacklinks
set creator = i.newusername
where creator = i.oldusername;

update trackbacklinks
set lastmodifier = i.newusername
where lastmodifier = i.oldusername;

update OS_PROPERTYENTRY
set entity_name = replace(entity_name,i.oldusername,i.newusername)
where entity_id = (select entity_id from external_entities where entity_name = i.newusername);

end loop;
end;

Posted by donald.jennings@celera.com at Aug 22, 2006 15:34

The Article is outdated.

As Donald wrote, there are some new Tables which are not mentioned. Here is my SQL-Code running in Version 2.2.8, with MySQL 4.1:

update attachments x inner join usermigration u on
x.creator = u.oldusername
set x.CREATOR = u.newusername;

update attachments x inner join usermigration u on
x.lastmodifier = u.oldusername
set x.lastmodifier = u.newusername;

update content x inner join usermigration u on
x.lastmodifier = u.oldusername
set x.lastmodifier = u.newusername;

update content x inner join usermigration u on
x.creator = u.oldusername
set x.creator = u.newusername;

update content x inner join usermigration u on
x.username = u.oldusername
set x.username = u.newusername;

update content_label x inner join usermigration u on
x.owner = u.oldusername
set x.owner = u.newusername;

update draft x inner join usermigration u on
x.owner = u.oldusername
set x.owner = u.newusername;

update extrnlnks x inner join usermigration u on
x.creator = u.oldusername
set x.creator = u.newusername;

update extrnlnks x inner join usermigration u on
x.lastmodifier = u.oldusername
set x.lastmodifier = u.newusername;

update label x inner join usermigration u on
x.owner = u.oldusername
set x.owner = u.newusername;

update links x inner join usermigration u on
x.lastmodifier = u.oldusername
set x.lastmodifier = u.newusername;

update links x inner join usermigration u on
x.creator = u.oldusername
set x.creator = u.newusername;

update notifications x inner join usermigration u on
x.username = u.oldusername
set x.username = u.newusername;

update notifications x inner join usermigration u on
x.lastmodifier = u.oldusername
set x.lastmodifier = u.newusername;

update notifications x inner join usermigration u on
x.creator = u.oldusername
set x.creator = u.newusername;

update pagetemplates x inner join usermigration u on
x.lastmodifier = u.oldusername
set x.lastmodifier = u.newusername;

update pagetemplates x inner join usermigration u on
x.creator = u.oldusername
set x.creator = u.newusername;

update spaces x inner join usermigration u on
x.lastmodifier = u.oldusername
set x.lastmodifier = u.newusername;

update spaces x inner join usermigration u on
x.creator = u.oldusername
set x.creator = u.newusername;

update spacepermissions x inner join usermigration u on
x.permusername = u.oldusername
set x.permusername = u.newusername;

update spacepermissions x inner join usermigration u on
x.lastmodifier = u.oldusername
set x.lastmodifier = u.newusername;

update spacepermissions x inner join usermigration u on
x.creator = u.oldusername
set x.creator = u.newusername;

update contentlock x inner join usermigration u on
x.lastmodifier = u.oldusername
set x.lastmodifier = u.newusername;

update contentlock x inner join usermigration u on
x.creator = u.oldusername
set x.creator = u.newusername;

--update emailtemplates x inner join usermigration u on
--x.lastmodifier = u.oldusername
--set x.lastmodifier = u.newusername;

--update emailtemplates x inner join usermigration u on
--x.creator = u.oldusername
--set x.creator = u.newusername;

--update emailtemplates x inner join usermigration u on
--x.username = u.oldusername
--set x.username = u.newusername;

update os_user x inner join usermigration u on
x.username = u.oldusername
set x.username = u.newusername;

update trackbacklinks x inner join usermigration u on
x.lastmodifier = u.oldusername
set x.lastmodifier = u.newusername;

update trackbacklinks x inner join usermigration u on
x.creator = u.oldusername
set x.creator = u.newusername;

Hint:

  • as there can be duplicates in the new username-list, run all SQL-Code except of os_user-update.
  • run only os_user, if there is an error, change the problematic newusername
  • After running the scripts make a full reindexing, to have affect on the recentlyupdated section for example
Posted by fst at Sep 11, 2006 10:32

Is there a way to do this with the standalone server? Thanks.

Posted by cl@escenic.com at Sep 25, 2006 03:38

When Confluence is shut down, the standalone database is stored in a text file called confluencedb.log in confluence-home/database. So after shutting down Confluence, you can do a search and replace in this text file to update a username.

Please make sure you take a backup copy of the database before making any changes, as recommended above. For standalone, this means taking a complete copy of your confluence-home directory.

Posted by mryall at Sep 26, 2006 17:43

I found that I had to modify the confluencedb.script file as well as the confluence-home/database/confluencedb.log file (infact, after making changes in both, it looked like the confluencedb.log file got reset after restarting Confluence, so perhaps the only place requiring the change is confluencedb.script).

Posted by stephenmorad at Nov 17, 2006 11:48

I rewrote for SQL Server

UPDATE attachments 
SET attachments.creator = usermigration.newusername
FROM usermigration 
    INNER JOIN attachments 
    ON (usermigration.oldusername = attachments.creator)

UPDATE attachments 
SET attachments.lastmodifier = usermigration.newusername
FROM usermigration 
    INNER JOIN attachments 
    ON (usermigration.oldusername = attachments.lastmodifier)

UPDATE content 
SET content.creator = usermigration.newusername
FROM usermigration 
    INNER JOIN content 
    ON (usermigration.oldusername = content.creator) 

UPDATE content 
SET content.lastmodifier = usermigration.newusername
FROM usermigration 
    INNER JOIN content 
    ON (usermigration.oldusername = content.lastmodifier) 

UPDATE content 
SET content.username = usermigration.newusername
FROM usermigration 
    INNER JOIN content 
    ON (usermigration.oldusername = content.username) 

UPDATE content_label 
SET content_label.owner = usermigration.newusername
FROM usermigration 
    INNER JOIN content_label 
    ON (usermigration.oldusername = content_label.owner) 

UPDATE draft 
SET draft.owner = usermigration.newusername
FROM usermigration 
    INNER JOIN draft 
    ON (usermigration.oldusername = draft.owner) 

UPDATE extrnlnks 
SET extrnlnks.creator = usermigration.newusername
FROM usermigration 
    INNER JOIN extrnlnks 
    ON (usermigration.oldusername = extrnlnks.creator) 

UPDATE extrnlnks 
SET extrnlnks.lastmodifier = usermigration.newusername
FROM usermigration 
    INNER JOIN extrnlnks 
    ON (usermigration.oldusername = extrnlnks.lastmodifier) 

UPDATE label 
SET label.owner = usermigration.newusername
FROM usermigration 
    INNER JOIN label 
    ON (usermigration.oldusername = label.owner) 

UPDATE links 
SET links.creator = usermigration.newusername
FROM usermigration 
    INNER JOIN links 
    ON (usermigration.oldusername = links.creator) 

UPDATE links 
SET links.lastmodifier = usermigration.newusername
FROM usermigration 
    INNER JOIN links 
    ON (usermigration.oldusername = links.lastmodifier) 

UPDATE notifications 
SET notifications.creator = usermigration.newusername
FROM usermigration 
    INNER JOIN notifications 
    ON (usermigration.oldusername = notifications.creator) 

UPDATE notifications 
SET notifications.lastmodifier = usermigration.newusername
FROM usermigration 
    INNER JOIN notifications 
    ON (usermigration.oldusername = notifications.lastmodifier) 

UPDATE notifications 
SET notifications.username = usermigration.newusername
FROM usermigration 
    INNER JOIN notifications 
    ON (usermigration.oldusername = notifications.username) 

UPDATE pagetemplates 
SET pagetemplates.creator = usermigration.newusername
FROM usermigration 
    INNER JOIN pagetemplates 
    ON (usermigration.oldusername = pagetemplates.creator) 

UPDATE pagetemplates 
SET pagetemplates.lastmodifier = usermigration.newusername
FROM usermigration 
    INNER JOIN pagetemplates 
    ON (usermigration.oldusername = pagetemplates.lastmodifier) 

UPDATE spaces 
SET spaces.creator = usermigration.newusername
FROM usermigration 
    INNER JOIN spaces 
    ON (usermigration.oldusername = spaces.creator) 

UPDATE spaces 
SET spaces.lastmodifier = usermigration.newusername
FROM usermigration 
    INNER JOIN spaces 
    ON (usermigration.oldusername = spaces.lastmodifier) 

UPDATE spacepermissions 
SET spacepermissions.creator = usermigration.newusername
FROM usermigration 
    INNER JOIN spacepermissions 
    ON (usermigration.oldusername = spacepermissions.creator) 

UPDATE spacepermissions 
SET spacepermissions.lastmodifier = usermigration.newusername
FROM usermigration 
    INNER JOIN spacepermissions 
    ON (usermigration.oldusername = spacepermissions.lastmodifier) 

UPDATE spacepermissions 
SET spacepermissions.permusername = usermigration.newusername
FROM usermigration 
    INNER JOIN spacepermissions 
    ON (usermigration.oldusername = spacepermissions.permusername) 

UPDATE contentlock 
SET contentlock.creator = usermigration.newusername
FROM usermigration 
    INNER JOIN contentlock 
    ON (usermigration.oldusername = contentlock.creator) 

UPDATE contentlock 
SET contentlock.lastmodifier = usermigration.newusername
FROM usermigration 
    INNER JOIN contentlock 
    ON (usermigration.oldusername = contentlock.lastmodifier) 

UPDATE trackbacklinks 
SET trackbacklinks.creator = usermigration.newusername
FROM usermigration 
    INNER JOIN trackbacklinks 
    ON (usermigration.oldusername = trackbacklinks.creator) 

UPDATE trackbacklinks 
SET trackbacklinks.lastmodifier = usermigration.newusername
FROM usermigration 
    INNER JOIN trackbacklinks 
    ON (usermigration.oldusername = trackbacklinks.lastmodifier) 

UPDATE os_user 
SET os_user.username = usermigration.newusername
FROM usermigration 
    INNER JOIN os_user 
    ON (usermigration.oldusername = os_user.username) 

go
Posted by bpair at Jan 05, 2007 13:58
Document generated by Confluence on Feb 08, 2007 00:00