Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / upgraded from 2010 wan...
Microsoft Dynamics GP (Archived)

upgraded from 2010 want to remove all old users and start from scratch

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hello,

I am in the process of upgrading from 2010 to a new server. I restore the company databases and ran utilities to upgrade to 2015. I have a lot of old unneeded users and just want to start from scratch and remove all the old users and start from scratch. Is there a SQL script that will do this? I assume after blowing all the users away I will need to re-create DYNSA and sa. Is there instructions on how to clean up all the users and start again with new ones? Thank You

*This post is locked for comments

  • Suggested answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    RE: upgraded from 2010 want to remove all old users and start from scratch

    The GP Power Tools has a function that can do that :-) Yes it Can !... delete all the SQL users in the server security (and their company assignment of course)... Then in GP, with Power Tools you can recreated all the SQL users with a new default password, based on the stored GP logins from the SY01400 table.. and their respective company assignment.

    This pretty much removes the need of the KB from Microsoft to recreate all the SQL security logins and even better, old 'ghost' user id's that have been orphaned for many years are getting cleared out in the process.

    The tools used for this is called 'Database Validation' from the GPPT routines menu.. Check the user guide at page 160.

    Note : Database validation does much more than just that... so it's 100 times worth the cost of the license (1$/day).. you can even get a 30 day trial key from Mekorma.

  • Suggested answer
    L Vail Profile Picture
    L Vail 65,271 on at
    RE: upgraded from 2010 want to remove all old users and start from scratch

    Hi,

    Here's a script that might help if you can do them one by one:

    --Remove a user from all databases on the server

    EXEC sp_Msforeachdb "IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'baduser')

    DROP USER [baduser]"

    GO

    --Drop the login

    IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'baduser')

    DROP LOGIN [baduser]

    GO

    Kind regards,

    Leslie

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: upgraded from 2010 want to remove all old users and start from scratch

    That would work but you would still need to remove them from the DYNAMICS..SY01400 TABLE. Otherwise you would have trouble in the future if you ever tried to use the same ID again. The cleanest way to do this would be to use the capture users scripts and get them all established on the new server and then use GP to delete the users. That way you cleanup GP and SQL in one step. Any orphaned users you can clean up manually.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: upgraded from 2010 want to remove all old users and start from scratch

    ok it looks like I only have 18 users can I just go into SQL Management Studio Security -> Users right click and delete them? There is some inconsistency between DYNAMICS and the two company databases, too, not sure how that happened.

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: upgraded from 2010 want to remove all old users and start from scratch

    The only drawback would be if you remove someone who should not be removed. Then you would owe them lunch.

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: upgraded from 2010 want to remove all old users and start from scratch

    You would run the capture logins scripts against the old server and then the modified version against each database on the new server. Then you will need to remove user records from the DYNAMICS..SY01400 table. Then run Tools->Utilities->System->Reconcile on everything to clear out the associated user records in other tables.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: upgraded from 2010 want to remove all old users and start from scratch

    So would I run the capture logins script against the old sql server or the new one? Could there be any drawbacks from wiping out all my users?

    Thanks for your help

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: upgraded from 2010 want to remove all old users and start from scratch

    You could use the capture logins scripts from KB878449 and after it creates the scripts edit the script by replacing all CREATE LOGINS with DROP USER against each company database.

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans