Automating User Enablement after Database Refresh Using SQL Script

(1) Share

Report

Posted on by Community member

Hello All,
We have around 15-20 environments where database is refreshed twice a month. After database refresh, all users are disabled by default, and only admin is enabled. This is expected behavior.
Our customer wants to enable only a specific set of users in non-prod environment. For e.g. 50/100
Currently, we’re exporting list of enabled user from UserInfo form, and after database refresh, we're enabling each user manually from user info form.
Is there a quick way to achieve it via sql script?

I have the same question (0)

All responses (5)

Answers (2)

Sort by

Verified answer

Martin Dráb 239,040 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Yes, of course that you can use SQL to manipulate data in SQL databases.

I think you just need to change the value of Enable field in UserInfo table.

Was this reply helpful? Yes No
Verified answer

Community member 7 on at

Like (0)

Report
Copy link

Link copied!

Thanks for your reply. I agree we can turn the value from 0 to 1 in field enabled. For e.g.

Update userinfo set enable=1 // this statement will enable all the users in table.

But our situation is a bit tricky. We don’t want to enable all the users. We have a list of users that needs to be enabled.

We're thinking to run a sql script before database refresh, and the output of script should be run on database after refresh to enable only users which was enabled before DB refresh. Can this be achieved by sql script?

Was this reply helpful? Yes No
Suggested answer

Mohit Rampal 12,573 Moderator on at

Like (1)

Report
Copy link

Link copied!
Hi, If you want specific users to enable then probably you can create a User Group and add users that should be enabled after database refresh. Create sql script to enable only users added in that user group.

To enable all users, you can use below sql script

update UserInfo set Enable = 1

To enable specific users you can use below sql script. Replace 'Test' with GroupId

update UserInfo set Enable = 1 From UserInfo join userGroupList On userGroupList.USERID = UserInfo.ID AND userGroupList.GROUPID = 'Test'

1 people found this reply helpful.

Was this reply helpful? Yes No
Suggested answer

Community member 14 on at

Like (0)

Report
Copy link

Link copied!

@Martin Dráb on 10 Jul 2023 at 22:33:08 , Thanks for your reply. I agree we can turn the value from 0 to 1 in field enabled. For e.g.

Update userinfo set enable=1 // this statement will enable all the users in table.

But our situation is a bit tricky. We don’t want to enable all the users. We have a list of users that needs to be enabled.

We're thinking to run a sql script before database refresh, and the output of script should be run on database after refresh to enable only users which was enabled before DB refresh. Can this be achieved by sql script?

Was this reply helpful? Yes No
Community member 14 on at

Like (0)

Report
Copy link

Link copied!

@Mohit Rampal on 11 Jul 2023 at 12:00:46

Thank you very much Mohit Rampal.

Was this reply helpful? Yes No