I sometimes get questions about whether the 'sa' user is required for Microsoft Dynamics GP, what an alternative for 'sa' is, and what the POWERUSER Security Role really does. Well, I'm not a Dynamics GP developer at Microsoft, but here is what I know from what I've seen. Keep in mind this is not a How-To for setting up GP security.
Before going into the differences of each type of user or role, let me explain a little about GP Security.
During the initial installation and each subsequent company database creation, a database role called DYNGRP is created in each database and assigned permissions to most objects in each database (Tables, Views, and Stored Procedures). This one database role is the basis for all SQL database access for GP users - with the exception of the 'sa' user, but more on that later.
When you create a new user in GP, the following key actions take place, not necessarily in this order and not limited to this list:
- A SQL login is created
- A GP user is created in DYNAMICS..SY01400
- The user is assigned to the DYNGRP database role in the DYNAMICS database
- The user is assigned to the DYNGRP database role in each respective company given to it in the User Access window (after the user is created)
- The user's password is encrypted and saved in SQL
Now on to the 'sa' and DYNSA users, internal GP Security, the POWERUSER Security Role, and Alternate/Modified Forms and Reports settings.
sa
This is the big cheese of both SQL and GP. There is no greater user than this and there is no GP data it cannot touch. "There is the GP system password," the semi-experienced might say. Well the 'sa' user does not need to be logged in to GP to get to the SQL tables protected by the GP system password.
The ‘sa’ user is also the only GP user who has direct access to the databases from SQL and is the only SQL user that is created outside GP that can log into GP. A special static ‘sa’ user is built into GP, and it cannot be deleted nor have its password changed from inside GP. The 'sa' user is the only GP user who can perform all GP maintenance and administrative tasks inside GP* as well as all maintenance and administrative tasks in SQL. By default, 'sa' has the POWERUSER Role in all companies.
DYNSA
All GP SQL databases are owned by the DYNSA user. This is what allows the DYNSA user to perform most administrative and maintenance tasks without requiring the ‘sa’ user.
EDIT: This also allows DYNSA to back up company databases inside GP (but cannot restore them - only 'sa' can restore databases). GP adds DYNSA to the SecrityAdmin and dbCreator system roles on installation, so this user can administer GP users but still does not allow creating new GP companies despite the dbCreator role.
System Password
There are certain system-wide administrative tasks that are protected by GP's System Password, not to be confused with the 'sa' password. The System Password protects areas such as GP Security, Multicurrency Access, and other tasks not related to any particular company. You can log in as 'sa' or DYNSA, but if you do not have the System Password you cannot perform those tasks inside GP. Keep in mind that users do not have to be 'sa' or DYNSA to perform some of those tasks as long as they have the System Password.
Internal GP Security
Dynamics GP's security is what keeps regular GP users from having access to all objects in all GP databases given through the DYNGRP SQL database group.
What I describe here relates to Dynamics GP version 10 and above. Version 9 and below security was completely different.
GP Security Operations
Operations are the individual windows and reports to which security access can be given. For example, the Payables Transaction Entry window and Financial Detail Trial Balance Report are Operations.
Operations are defined by the available windows ("forms," in GP terminology) and reports in a given product dictionary. This is not something you will set up as a GP administrator.
GP Security Tasks
Tasks are small groups of Operations that relate to each other. For example, the ADMIN_PURCH_001 task gives access to several windows and reports relating to setting up Purchasing.
GP Security Roles
Roles are groups of Tasks that define a job role. For example, the AP Clerk role allows a user to enter Payables Transactions, maintain Vendors, look up GL entries, and print Purchasing reports.
There are plenty of pre-defined Tasks and Roles available out of the box. I suggest that instead of editing the defaults you create new Tasks and Roles based on the default ones. This will help you or your Microsoft Partner identify which ones are non-standard if you run into any issues related to security.
When giving access to users, you will be specifying which Role(s) they will have and for which company. A user can have multiple Roles and can have different Roles in each GP company.
POWERUSER Role
Unlike the AP Clerk Role where its access is explicitly defined, the POWERUSER Role implicitely has access to everying the DYNGRP database role has access to in a specified GP company database. The 'sa' and DYNSA users are automatically part of the POWERUSER Role. Giving a regular user all other Security Roles is not the equivalent of the POWERUSER Role.
Alternate/Modified Forms and Reports Settings
In Dynamics GP version 9 and below, setting someone up to use a modified report was very granular and did not allow for very easy administration. In version 10 and later, it is like setting up a group of on/off switches: for each alternate or modified option available on the workstation you are using to set this up, you will specify whether to use the default or the alternate/modified alternative. Yes, since you can technically be using different report dictionaries and different modules per workstation, the Alternate/Modified Forms and Reports settings are workstation-specific; most of the time you'll want to utilize the same modules and a shared location for modified reports, but that's not always the case. Alternate/Modified Forms and Report settings can be specified per user per company.
*Adding Additional System Security
As noted by Devo (thanks for that find!), additional security can be added to users to perform certain administrative tasks, such as adding users and companies. See pages 37-40 of the GP Security Planning guide found here.
EDIT: To give users access to be able to create new users or update passwords, assign the SQL login to the SecurityAdmin server role. To give users access to be able to add new companies in GP Utilities, assign the SQL login to the sysadmin server role.
Conclusion
I hope this helps answer at least a few questions regarding how Microsoft Dynamics GP and Microsoft SQL Server work together to provide security for Dynamics GP as well as clarifies how security is implemented inside GP.
If you found this helpful, please leave a comment.
