In most cases logging in as ‘sa’ is not required for Dynamics GP. The exceptions are really installing/upgrading products and possibly troubleshooting a specific issue. The Professional Services Tools no longer require ‘sa’. Third party applications should not be requiring ‘sa’ either.

The point is that using ‘sa’ should be a rare exception, not the norm.

Admittedly, ‘sa’ is incredibly convenient sometimes. So is parking in a fire lane. That doesn’t mean that it’s a good idea.

A while back at a conference Nicole Albertson of eOne and I were talking about not using the ‘sa’ user and using a different GP user to create users. She brought up the central point of this piece, but I can’t find that she ever wrote about it. (If she did, feel free to add a link in the comments).  It’s been long enough that her thoughts have mingled with mine and I want to make sure that she gets credit.

In most cases logging in as ‘sa’ is not required for Dynamics GP. The exceptions are really installing/upgrading products and possibly troubleshooting a specific issue. The Professional Services Tools no longer require ‘sa’. Third-party applications should not require ‘sa’ either. The point is that using ‘sa’ should be a rare exception, not the norm. Admittedly, ‘sa’ is incredibly convenient sometimes. So is parking in a fire lane. That doesn’t mean that it’s a good idea.

One place where people are often genuinely confused is creating new users. Any user in GP can be configured to add, administer, and delete users. The ‘sa’ user is not required. The Dynamics GP Planning for Security document, (Page 35, Creating User Records) offers a number of options for allowing any GP user to administer users. Most of these come down to variations on assigning a user to a fixed SQL Server role. The simplest, and most flexible, option is to assign a user to the SQL Server SYSADMIN role and ensure that the user is a member of DYNGRP (Option 2).

When they first see this option, most DBA’s recoil reflexively and default to the word “no”. The other fixed role options generate a similar response. The problem is that DBA’s are missing a piece of the puzzle that is unique to GP.

DBA’s hate the idea of assigning a user to the SYSADMIN role because designating a user as a SYSADMIN typically gives them total control over a database, but that is not the case with a GP user. The GP user’s password is encrypted prior to sending it to SQL. This means that the password that SQL sees is not the same as their GP password. When a user logs into GP, their password is unencrypted to allow GP to interact with SQL. Consequently, GP users cannot directly access SQL with their GP login credentials. It can’t be done. I’ve bolded that line so that you can show it to your DBA.

A GP user’s access to the power of SYSADMIN is limited to those tasks in GP that require SYSADMIN rights. The user is constrained in that they can only interact with SQL within the tasks available in GP. Another indicator of this is that changing a GP user’s password at the SQL level breaks their access to GP.

As a test for your DBA, create a GP login with some basic inquiry rights. Set that user as a member of the SYSADMIN role in SQL. Feel free to use your test environment if that makes them more comfortable. Give them the GP login and password and ask them to try to use SQL tools to access the database directly. It won’t happen.

Using the ‘sa’ user is dangerous, and not just because it offers total control. It also offers a level of anonymity. If multiple users have the ‘sa’ password, figuring out who used ‘sa’ to perform a task becomes extremely difficult, if not impossible. Once a DBA becomes comfortable that setting a few GP users as SYSADMINs won’t give them unfettered access to SQL, this option makes it much easier to limit the use of ‘sa’.