Microsoft Dynamics GP System Administrator: Should You Separate Accounting Controls and Duties?

May 4 2011

Over the years, we've had questions from larger clients about the Microsoft SQL "SA" (System Administrator) user.  The SA users, or other users attributed with system administration capabilities, are POWERUSERS inside SQL Enterprise, even to the point of enabling the editing of tables. As Microsoft's security planning guide for Dynamics GP (last updated in 2007) explains, "any user who is assigned to the POWERUSER security role will have access to everything in Microsoft Dynamics GP, with the exception of private lists."1  That means POWERUSERS, usually SA barring other changes to user security, have access or can grant themselves access to all tables and operations in Dynamics GP.  Certain operations can only be performed by POWERUSERS, per the listing below.

DYNSA is created by Dynamics Utilities and assigned to the POWERUSER security role during the initial installation of Dynamics GP and set as the "db_owner" of the Dynamics and company databases.  If that ownership is changed prior to or during upgrades, the upgrades will fail.  As Microsoft's security documentation explains, "f different owners are assigned, complications can arise when deleting user accounts and granting access to companies."2

Auditors point out that the broad access privileges of administrators like an SA user must be carefully managed and tracked. One all-powerful user with no accountability to auditors is unacceptable in view of accounting controls and separation of duties. With Dynamics GP, the accounting department must take charge of the system and invoke the typical auditing of the ERP software: balancing, testing, reconciling, and verification with outside sources.

Microsoft Dynamics GP's module Audit Trails sets up a separate SQL database that tracks user-defined add/change/delete activity in Dynamics GP. This database is separate from the DYNAMICS and company databases; therefore it can be secured from Dynamics GP users.

The Audit Trails database is installed in the same SQL instance as Dynamics GP; therefore, it can't be secured from the POWERUSERS within the same instance in which Dynamics GP resides.  Audit Trails, a part of the Risk Management Suite, is being regularly implemented now in Microsoft Dynamics GP. Audit Trails helps narrow the gap caused by the POWERUSER who has complete access to the ERP tables.

If SQL POWERUSERS don't have participation in day-to-day accounting and finance functions, and accounting and financial personnel don't have access to Audit Trails, and auditors have only reporting access from either Dynamics GP or Audit Trails, the audit gap-the risk-is relatively small.

Other alternatives to the Risk Management Suite:

  1. One client developed a custom front-end login program to SQL where no one knows the SA password. Two people must login to the custom program, the program inputs the SA password to SQL Enterprise Manager/Studio, and then when the users have completed their process, their logout changes the SA password.
  2. I've also worked with clients who make the SA password 6-8 characters long. One IT person has half; one accounting person has half. Both must be present to type in their half to gain access with SA.
  3. And lastly, for one of our clients, Computeration is the only one with the SA password and we are the only ones who perform SA-restricted processes. One of our employees knows the password, I hold it in a sealed envelope in our fireproof cabinet, and their CEO holds it in a sealed envelope.  Of course, we have no access to their bank, receivables, payables, cash, or any other accounting operation.

In spite of what accounting standards recommend with regard to segregation of duties, there are certain tasks that must be performed by SA, or other users attributed with system administration capabilities, in Dynamics GP.   As you'll see in the list below, these are the unavoidable administrative duties involved in running a complex enterprise software solution - duties that are usually beyond the scope of accountants or other accounting department users.

Our clients have chosen not to add system administration capabilities to additional user accounts, so these are the typical procedures we use the SA user to perform because POWERUSER security, Dynamics system security, or access over multiple company databases is required.   Also, as SQL security documentation from Microsoft is updated periodically there may be additions and exceptions to the following list. 

  • Run all these applications through Dynamics Utilities
    • Apply service packs, hot fixes
    • Process an upgrade
    • Create a company database
    • Remove the SOP and Invoice message
  • Run installations of new code for modules or third-party products
  • Create tables when activating certain new modules
  • Add/change/delete Intercompany
  • Add/change/delete Currencies, Exchange Tables, and Multicurrency Access
  • Delete a company (deletes the list record but doesn't drop the database)
  • Add/change/delete a user
  • Add/change/delete user access to a company
  • Add/change/delete tasks to security roles
  • Add/change/delete security roles
  • Assign user security
  • Run any Maintenance
    • Clear Data
    • Check links
    • Backup
    • Restore
    • Updates Payroll Tax Tables
    • SQL Maintenance
      • Recompile
      • Update Statistics
      • Drop table
      • Create table
      • Drop auto procedure
      • Create auto procedure
  • Re-sequence contracts in Contract Administration

1 Page 28, Microsoft Dynamics GP Planning for Security, copyright Microsoft, 2007.

2 Ibid

FREE Membership Required to View Full Content:

Become a MemberLogin
Joining gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more, and it’s all FREE. You’ll also have the option to receive periodic email newsletters with the latest relevant articles and content updates. Learn more about us here
About Gloria Braunschweig

Gloria has experience across the full spectrum of business operations and management. Decades of experience are documented in the book Rapid Implementation, establishing Gloria as a specialists using Microsoft SQL tools for implementation, integration, and business intelligence related to Microsoft Dynamics GP.

Gloria writes and presents on lean implementation concepts and business management systems for small and mid-size businesses. 

More about Gloria Braunschweig


adonat's picture

My recommendation is to take a look at page 37 of the Planning for Security .pdf file. #5 on that page explains what database roles are needed to be able to administer GP User accounts within the SQL Server and Dynamics GP application. You do not need SA or DYNSA to push this functionality down to the business application administrator. In addition, the DYNSA password is not set to the SA password. If that was the case, the SA account would no longer function as we apply a second level of encryption to the password prior to adding the account to SQL. Thanks! Aaron Donat Sr. Escalation Engineer Dynamics GP

Gloria Braunschweig's picture

I stand corrected about the DYNSA password. The options presented in the security paper allow attribution of certain typical-SA procedures to other users. It doesn't eliminate the overall weakness that the SA remains an all-powerful user.