A big part of my job also involves administering our company’s deployment of Microsoft Dynamics Great Plains. This is the ERP system of choice for many medium to large organizations. Needless to say it sits on top of a SQL Server database.
One thing that always bugged me about GP was the fact that even as a power user, there were routine tasks that required me to log in under the ‘sa’ account. Specifically, creating new user accounts and modifying existing user permissions. There were dozens of other tasks that required me to log in as ‘sa’ but these were the most common. As any Systems Administrator/Analyst knows, ‘sa’ should rarely, if ever be used for day-to-day logins. The main problem is that there are three people on my team. Each of us could log in as ‘sa’ at any time and make a change that could disrupt the system. Audit tracking would only tell us that ‘sa’ made the change. It was impossible to know who actually made the change.
So with this problem in mind I began researching how I could assign ‘sa’ level permissions to an individual user’s account. Since the average GP user won’t need this level of access the only thing you need to ensure on the front-end is that the user account to which you would like to grant ‘sa’ level permissions is set as a poweruser within GP’s security module. Once that has been done it’s just a matter of making some changes to that user’s server roles and database role membership in SQL Server.
The first step is to provide the user account access to the securityadmin server role. To do this open SQL Management Studio and expand the Security folder and then the Logins subfolder. Locate the user account and open the Login Properties. Select the Server Roles page and click the checkbox next to securityadmin. Then click OK.
The next step is to assign the database user to the db_accessadmin and db_securityadmin roles. You will need to do this on the DYNAMICS database as well as all GP company databases. In my environment we only have a single company database so I would only need to update the user’s permission on two databases.
From the Databases folder in SQL Management Studio expand the database where you will be updating the user’s permissions. For this example I will be using the DYNAMICS database. Once expanded, then expand the Security folder then the Users folder. Locate the individual user and open the Database User window.
Select the Membership page and click the checkbox next to db_accessadmin and db_securityadmin. You will see in the following screenshot that db_datareader, db_datawriter and DYNGRP are also checked. These roles should already be available if the user has been set up as a poweruser. If for some reason these are not selected, go ahead and check them. Click OK.
You will also want to make the same change on all of your company databases. Once that has been completed, the user account should now have ‘sa’ level permissions and should be able to complete all ‘sa’ level tasks within the Great Plains application.