Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Answered

Automating User Enablement after Database Refresh Using SQL Script

(1) ShareShare
ReportReport
Posted on by 14
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?
  • Community member Profile Picture
    Community member 14 on at
    Automating User Enablement after Database Refresh Using SQL Script in d355 fo
    @Mohit Rampal on 11 Jul 2023 at 12:00:46
     
    Thank you very much Mohit Rampal.
  • Suggested answer
    Community member Profile Picture
    Community member 14 on at
    Automating User Enablement after Database Refresh Using SQL Script in d355 fo
    @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?
  • Suggested answer
    Mohit Rampal Profile Picture
    Mohit Rampal 12,554 Super User 2024 Season 1 on at
    Automating User Enablement after Database Refresh Using SQL Script in d355 fo
    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'
     
     
  • Verified answer
    Automating User Enablement after Database Refresh Using SQL Script in d355 fo

    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? 

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,848 Most Valuable Professional on at
    Automating User Enablement after Database Refresh Using SQL Script in d355 fo
    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Congratulations to the January Top 10 leaders!

Check out the January community rock stars...

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,979 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,848 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans