This page last changed on Mar 01, 2007 by ivan@atlassian.com.

A username is the name used to login to Confluence, eg. jsmith. There is no support for changing a username via Confluence yet, but you can to vote towards a feature request to allow usernames to be changed from the web interface.

Instructions For Changing Usernames

Usernames can only be changed through direct update to the Confluence database.

  1. If you have a database administrator, request that they approve the database-related steps described below

  2. If you are using JIRA user management, Revert from JIRA To Internal User Management

  3. Backup Confluence

  4. Creating a usermigration table:
    create table usermigration
    (
    oldusername varchar,
    newusername varchar
    )
  5. Usernames that will be changed must be placed in the usermigration table with their current and planned usernames:
    insert into usermigration (oldusername, newusername)
    values ('oldusername', 'newusername');
  6. If your DB administration tool does not support multiple SQL queries, these must be entered individually.
    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 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;
  7. If using Confluence 2.1 or newer, run the following command:
    update user
    set name = newusername from usermigration u
    where name = u.oldusername;
  8. Reassign personal spaces associated with the old username to the new username. The tilda (~) is required as it is prepended to the space key of all personal spaces.
    update spaces
    set spacekey = '~newusername'
    where spacekey = '~oldusername';
  9. Each username is associated with a full name. For example, username 'jsmith' may have a full name of 'John M Smith'. If this fullname needs to be changed, modify the fullname in the user or os_user table.

All old usernames in Confluence should now be replaced with the new usernames from the usermigration table.

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 Mar 22, 2007 20:55