how can I add user to jira group using sql server ?

[Marine Tm]

Hello, 

is there a way to add or delete user into a Jira group using sql server ? if yes how please ?

Thank you !

[Nic Brough -Adaptavist- · Accepted solution]

Yes, you can do this, but you REALLY do not want to do it this way.

The process is

• Stop Jira
• Take a full backup
• Restore the backup to a test server to make sure it is valid
• Run the SQL against the database and hope it is valid (there's more than one table you'll need to update - do you know which ones you need to touch and how to do it right?)
• Restart Jira
• Run a full locking re-index of the entire system 

Are you really sure this is the best option?  When you could do it perfectly safely and without taking anything offline via the UI, or REST?

[Marine Tm]

Hello @Nic Brough -Adaptavist- ,

i must do it with sql but, i don't know how ..

[Jonas Ekström]

Hi,

Is it possible to insert straight into the database? Yes, it's always possible to add data into a database. I'd personally advice against it.
If changes are to be made programatically I'd do it via REST API instead of straight into a database.

https://docs.atlassian.com/software/jira/docs/api/REST/7.6.1/#api/2/user
https://docs.atlassian.com/software/jira/docs/api/REST/7.6.1/#api/2/group

[Marine Tm]

hello,

do you have a sql query ?

[Jonas Ekström]

I don't, and I'd really advice against it.
What do you need to do it via SQL?

What prevents you from doing so via REST calls?

[Marine Tm]

@Jonas Ekström  

this is the specifications of my team, they would do it only with sql

[Jonas Ekström]

If I was in your situation, I'd go back to the team and explain why this is a terrible idea.

This would result in downtime of the application for each user insert, risk of inconsistencies, and the need for a complete rollback of the database for any issue.

Is it a recurring task that needs to be done often?
Is it many users at the time?

In case you really want to risk your database and accept downtime every update then go ahead and knock yourself out with the developer reference material https://developer.atlassian.com/server/jira/platform/database-user-and-group-tables/#group-membership to try to find all the right references in the DB.

Doing this via API would get the right info into the right place, it would enable changes to be done without downtime.

[Nic Brough -Adaptavist-]

Your team are wrong, there's no nice way to put that.  I would want to question why they think they want to do this in such a bad way.

Tell them what I said above, tell them that it's a long, painful and risky thing to do and leaves them unsupported.  Tell them that if (when) it goes wrong, they're going to be blamed for the data loss and the loss of time when you have to roll back.  Plus the cost of the time you're wasting having to ask for more here, plus all the time you'll need to write, test and fully understand all the SQL you'll have to write.

Point out that there are fully supported, safe, documented ways of doing this in a couple of lines of code without having 8 hour downtime windows.

[Marine Tm]

@Nic Brough -Adaptavist- 

thank you so much for your answer and a advice 

and if my team accept to use rest api, is the any addons to install for doing this ? 

[Nic Brough -Adaptavist-]

No, you don't need anything extra, the REST API is automatically available alongside the UI.