Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP forum / Script to remove inact...
Microsoft Dynamics GP forum
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:
Install and Upgrade
  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,009 Super User 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,810 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

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,768 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,985 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans