Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Syntax errors generating CSVs for user and group import during cloud migration

Per I need to run the following SQL queries from the Jira Server and Confluence database to create CSV files of users and groups:


SELECT email_address AS "email", user_name AS "username", display_name AS "displayname", active AS "active" FROM cwd_user


SELECT DISTINCT parent_name AS group, child_name AS username FROM cwd_membership;


When the groups query is run (MS SQL Server 2016) I get an error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'group'.


Any ideas?


Also, i seen to be getting another error on one particular server when running the Users query:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'cwd_user'.

1 answer

1 accepted

2 votes
Answer accepted

Hi @Rob Horan 

The reason for failure is that Group is a reserved word for SQL. 

So try this version 

SELECT DISTINCT parent_name AS jiragroups, child_name AS username FROM cwd_membership;


With regard to the cwd_user this sounds like either a permission issue with the table or possibly that the table is missing which would be very unlikely if the solution is still running. 

I would start troubleshooting with checking the permissions on the table. If it is absent then you will know why you cannot include it in a query.

Hope this helps.


WOW - ok, so this worked in Dev, will check to see if this works in Prod.

Thanks @Phill Fox !

You wouldn't happen to know why I am also getting the Invalid object name 'cwd_user' error on the other query in Confluence by chance?

I had accidentally hit publish my answer before finishing. Have now added on the bit about cwd_user.  


Like Rob Horan likes this

Thanks @Phill Fox 

You were right about permissions.  Now oddly I am able to get info from Jira, but am unable to run the group query on Confluence:

SELECT DISTINCT parent_name AS groupname, child_name AS username FROM cwd_membership;

Msg 207, Level 16, State 1, Line 1
Invalid column name 'parent_name'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'child_name'.

Looking at dbo.cwd_membership, I don't see those column names... so in that sense the error makes sense, but then Atlassian's documentation doesn't.

image (34).png

Hi @Rob Horan 

Ah yes the delights of the confluence database structure. 

If you have not found it already the page is super useful especially if you match it to your version of Confluence. 

Using the SQL on for checking the user cloud limit as a starting point 

SELECT u.lower_user_name, d.directory_name, g.group_name FROM cwd_user u JOIN cwd_membership m ON = child_user_id JOIN cwd_group g ON m.parent_id = JOIN SPACEPERMISSIONS sp ON g.group_name = sp.PERMGROUPNAME JOIN cwd_directory d on u.directory_id = WHERE sp.PERMTYPE='USECONFLUENCE' AND = 'T' AND = 'T' GROUP BY u.lower_user_name, d.directory_name ORDER BY d.directory_name;

You should be able to manipulate this SQL to get the information you require from Confluence. 

Sorry but I am not currently in front of an instance to check it all completely for you.



Like Rob Horan likes this

Nothing to be sorry about, I'm incredibly grateful for all your help, thank you!  I'd buy you a pint on the spot if I could.

Suggest an answer

Log in or Sign up to answer
Community showcase
Asked in Atlassian Cloud Migration

AMA with Chris Clarke: Head of Product, Migration Journey

Happy  Migrations Month ! If you haven’t heard, this January we’re hosting exclusive events, giving away cloudies, and sharing helpful resources as part of our #CountdownToCloud celebration. H...

731 views 7 17
View question

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you