Dynamics GP California Partner Notes: how not to use SA account in Great Plains administration

December 23, 2017 by  Filed under: Computer 
 

If you have Microsoft Dynamics GP (formerly this ERP, MRP and Accounting application was known as Great Plains Software Dynamics GP and eEnterprise) implemented in your organization and your internal IT security is very tight, you may have this question: is it possible for GP administrator not to use MS SQL Server System Administrator account (sa).  

This is very big concern, especially when the same SQLServer 2008, 2005, or 2000 is hosting multiple DB Applications: Dynamics GP,Payroll Processing Database, Microsoft CRM, and others.  If this is your case, you do not wantDynamics GP administrator to be able to simply open New Query in SQL Server ManagementStudio and Select all the run select statement against your employees salariescodes.  In this small publication wewould like to offer you simple solution and highlights:

                                                                                     

1.       DYNSAaccount in Great Plains.  This account iswhat you need – it is Dynamics System Administrator, as Great Plains Softwaredesigned Dynamics for Microsoft SQL Server platform back in 1990th.  Many of the readers at this point willprobably go away with the decision to use this idea and be happy, however forthe rest of us – we would like you to come through checkup and troubleshootingprocedures and understand the architecture

 

2.       DYNSAin SQL Server Upgrades Transformations. If you deploy Great Plains long time, then you should know that youorganization in its history potentially might came through the followingupgrade: MS SQL Server 6.5 to 7.0, then from MS SQL Server 7.0 to 2000, thenfrom MS SQL Server 2000 to 2005, and then from MS SQL Server 2005 to 2008.  We recommend you to check is DYNSA login isstill member of the following Server Roles: dbcreator, public, securityadmin

 

3.       DYNSAin Test or New Companies Creation and Restore from Backup.  When your MS SQL Server Management Studio2005 or MS SQL Server Enterprise Manager 2000 are set to use sa account, and ifyou are using these tools to backup and restore Databases, then when yourestore the DB, database owner account will be changed to saautomatically.  In the day-to-day rushand stress of modern IT director life or in Dynamics GP internal support underthe pressure, you may not realize immediately the problem of kicking DYNSA userout of the scenes – your Dynamics GP system and its databases will continuefunction as expected, but from this moment you will have to use SA as DynamicsGP administrator user.  In order to resurrectDYNSA you will need to first run SP_HELPDB to understand who is current ownerof the DYNAMICS and companies DBSm and then you will need to reset ownership toDYNSA by running this statement: sp_changedbowner ‘DYNSA’

 

4.       Lasttip.  In Microsoft SQL Server 2005, DYNSAis not required to be in the DB users list, as it is DBO or DatabaseOwner.  However for older MS SQL ServerVersions: 2000, 7.0 and 6.5 this restriction might not be forced.  So, if you are getting error message inrunning sp_changedbowner ‘DYNSA’ script, please, open DB in SQL ManagementStudio, expand the DB  -> Security-> Users and delete DYNSA from the users list if it is there.  Then rerun sp_changedbowner ‘DYNSA’

 

5.       Atthis point you should be able to use DYNSA account in the same capacity as SA

 

6.       Ifyou have concerns or comments, please feel free to call us: 1-866-528-0577,help@albaspectrum.com

Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!


You must be logged in to post a comment.

Prev Post:
Next Post: