Solved

GP 2010 Upgrade - Ok to Delete Old Users?

Posted on 2013-05-13
4
365 Views
Last Modified: 2013-05-20
I upgraded a client to GP 2010 from GP 9.0 and in doing so, elected not to transfer over the security settings.  New users were created for GP 2010 and are currently being used.

I would like to remove the old users from the database.  Is there any reason for me not to remove the old users?  Can I remove them via MS SQL or do I need to do something special via GP 2010?

Thank you!
0
Comment
Question by:TacomaVA
  • 2
  • 2
4 Comments
 
LVL 18

Expert Comment

by:Steve Endow
ID: 39163499
Hi,

I can't think of any technical reason why you would need to retain the old users in GP.  The only thing that comes to mind might be some type of audit requirement that would require you to retain the accounts and perhaps disable them instead of deleting them--but I am not personally aware of any such standard audit requirement.

If the old users are still listed in GP, I would recommend deleting them through the GP user interface if you don't have a ton of users.  This would remove them from the GP security tables and remove their SQL user records from all of the SQL databases at the same time.  It's simple and clean, although a bit tedious to delete them one at a time.

If you have 75 or 100 or more users, then I could see doing it via SQL.  If you use SQL Management Studio, you'll need to remove the database users, remove the server logins, and then delete the user records in the GP database(s).

Looks like there are user ID related records in DYNAMICS in SY01400, SY01402, SY01403, SY01404, SY01600, and several others.  So it will take a little bit of up front work trying to figure out all of the tables that you would need to clean up.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%USERID%' ORDER BY TABLE_NAME


Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified IT Professional
0
 

Author Comment

by:TacomaVA
ID: 39168359
Thanks Steve.  I attempted to delete one of the old users via GP 2010, which worked, but I received a message notifying me that the user did not have a SQL login.  I opened SQL Management Studio and the user is still listed under Security -> Users and they have an entry in the Schema folder as well.

Can I delete the user and schema entry via SQL Management Studio without causing any harm?
0
 
LVL 18

Accepted Solution

by:
Steve Endow earned 500 total points
ID: 39168763
Hi,

Sorry, I think I jumped to a conclusion in my initial answer.

Did you move to a new SQL Server as part of your upgrade?

If so, and if you did not run the SQL scripts to specifically migrate your GP users to re-create them on the new server, then they are likely orphaned.

They will appear as the same user under the databases, but they will have behind-the-scenes SSIDs that are different from the SQL logins (because they came from the old server).  They are essentially orphaned security records in SQL Server.

Based on what you are describing where you get an error trying to delete the user from wihtin GP, it sounds like this is the case.  

If so, then I believe you will need to do both: delete users from GP, then manually clean out the SQL user records from each database.

Let me know if that sounds like the situation you are seeing.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified IT Professional
0
 

Author Closing Comment

by:TacomaVA
ID: 39181771
Thanks Steve - I did not transfer over the users.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a walkthrough guide I wrote whilst upgrading my on-premise MS Dynamics CRM 3.0 deployment to 4.0. This covers the actual installation of the product to a working level for my system, I ran into a lot of issues that the steps below fixed so h…
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question