web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
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
I have the same question (0)
  • Suggested answer
    Derek Albaugh Profile Picture
    Derek Albaugh on at

    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

  • Vanes2g Profile Picture
    Vanes2g 15 on at

    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

    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.......

  • DinB Profile Picture
    DinB 3,951 on at

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

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at

    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.

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 551 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 450 Super User 2025 Season 2

#3
BillurSamdancioglu Profile Picture

BillurSamdancioglu 278 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans