Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Script to remove inact...
Finance | Project Operations, Human Resources, ...
Suggested answer

Script to remove inactive GP accounts from all DBs in SQL Server

(0) ShareShare
ReportReport
Posted on by Vanes2g 15

Hi Community,

I am looking to make a script to remove all inactive GP accounts from all DBs in SQL server.  I have found on internet many different ways to do it but if somebody can send me an example of a tested script we can use it in our environment.

What I found so far is the following:

Remove user from a DB

SP_DROPUSER 'Useraccount'

ON MASTER DB

SP_DROPLOGIN 'Useraccount'

In the DYNAMICS DB

DELETE DYNAMICS..SY01400 WHERE USERID = 'Useraccount'
DELETE DYNAMICS..ACTIVITY WHERE USERID = 'Useraccount'
DELETE DYNAMICS..SY02000 WHERE USERID = 'Useraccount'
DELETE DYNAMICS..SY60100 WHERE USERID = 'Useraccount'

Is this correct?

The idea would be to create a script that gets the list of DBs where the useraccount currently is, and delete it from each of them.

If somebody can help me, I appreciate it.

Thank you,

Vanes2g

Categories:
Microsoft Dynamics GP
  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,054 Super User 2024 Season 1 on at
    RE: Script to remove inactive GP accounts from all DBs in SQL Server

    Hi Vanes2g,

    I'm scratching my head here while reading your question.. and like DinB, would ask: why can't you delete those users from the GP front-end ?

    Unless you've hundreds of GP companies and dozens of users to get rid, I'd not tap into SQL to remove all those users, unless you're already into a big mess due to some orphaned accounts that have been left on the roadside due to a GP server instance migration to a new SQL server... Even in that case, this would be a pure SQL security case and you would have to fix that part first.

    In the case above, where many user accounts in GP have been orphaned due to an improper system migration, there are ways of fixing them with SQL commands.

    As Derek listed, there are many tables in GP that reference the user ID, mainly related to the GP security and forgetting some of them could lead to some problems down the road later.

    When you remove a user within GP, the system takes care of dropping the user association with any company DB, removes all the security related entries and finally drops the user account from SQL security before completely removing the user entry in SY01400.

    You can test this out yourself by running a SQL trace from the backend when deleting an old 'inactive' user from the GP front-end and count all the tables that are hit by the process.

  • DinB Profile Picture
    DinB 3,812 on at
    RE: Script to remove inactive GP accounts from all DBs in SQL Server

    Are these damaged records? Why can't you delete them one by one from the User setup Window in GP?

  • Suggested answer
    Derek Albaugh Profile Picture
    Derek Albaugh on at
    RE: Script to remove inactive GP accounts from all DBs in SQL Server

    Whether you're trying to remove 'inactive' users that are idle from using a user license/logged into Dynamics GP or removing the 'inactive' users because they're no longer needed ever again in Dynamics GP nor SQL, I don't see why we would need to remove the users from the company databases for Dynamics GP completely.

    Even if you're removing the user from GP and SQL completely, we'd only need to remove the users from under 'User' in the GP databases in SQL, then remove their login from SQL.

    I'm thinking removing the users from the GP company databases, would cause issues.......

  • Vanes2g Profile Picture
    Vanes2g 15 on at
    RE: Script to remove inactive GP accounts from all DBs in SQL Server

    Hi Derek,

    Thank you for your reply.

    The information you posted is helpful, but we need something that runs for each GP DB automatically, finds the user and removes it.

    The first part should be getting the list of DBs that I already got, then creating a script that searches on each of them, and if the user exists, it should remove and pass to the next DB and do the same.

    I understand that for the MASTER DB and Dynamics DB, we have to run the commands you sent, but for the GP DBs is what I am trying to find the way.

    Any idea?

    Vanes2g

  • Suggested answer
    Derek Albaugh Profile Picture
    Derek Albaugh on at
    RE: Script to remove inactive GP accounts from all DBs in SQL Server

    No, those scripts will completely remove the user from Dynamics GP and SQL.......

    I guess it depends on what you mean by 'inactive'?

    --if 'inactive' meaning the users are idle and I just want to remove them from taking up a user license in Dynamics GP, then no, you don't want to use those........

    --if 'inactive' meaning these users are no longer used nor needed ever again, then yeah, the scripts would work.  I recommend these:

    >>If you're not able to delete the GP user through the User Setup window within Dynamics GP, you can manually remove them from these tables:

    DELETE DYNAMICS..SY01400 WHERE USERID = '<User_name>'

    DELETE DYNAMICS..ACTIVITY WHERE USERID = '<User_name>'

    DELETE DYNAMICS..SY60100 WHERE USERID = '<User_name>'

    DELETE DYNAMICS..SY10500 WHERE USERID = '<User_name>'

    DELETE DYNAMICS..SY10550 WHERE USERID = '<User_name>'

    The SY01400 is the User Master table and the SY60100 is the User Access table.

    The SY10500 and SY10550 table are the user security tables.

    >>If you need to remove the users from SQL Server, look under each of the GP system and company databases, under 'Users' to make sure any of these inactive users no longer exist there.

     Also, under Security > Logins, you can verify whether the user/login exists or not as well.

     The following script can also be run against the GP databases:

           sp_dropuser '<User_Name>'

     This script can be run against the Master system database to remove the login:

           sp_droplogin '<Login_Name>'

    The information above should completely remove the user from both Dynamics GP and SQL Server to the point where they could be re-added if at ever needed.

    Let us know.......

    Thanks

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

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey Pt 2

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,885 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,569 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans