The Microsoft Dynamics GP database security model as per the MS published paper “Microsoft Dynamics™ GP Planning for Security” This information is divided into the following sections.
1.) Password security
2.) DYNGRP database role
3.) SysAdmin fixed server role
4.) SQL Server fixed database roles beginning with “rpt_”
1.) Password security User accounts must be created within the Microsoft Dynamics GP application to ensure that security is applied to all Microsoft Dynamics GP windows and reports. Microsoft Dynamics GP encrypts the password during the user creation process before it is passed to Microsoft SQL Server. For example, if a user account is created with a password of ‘1234,’ before the user account is created in the Microsoft SQL Server, that password passes through the Microsoft Dynamics GP encryption process and is changed to something like ‘ABCD.’ When this happens, only the Microsoft Dynamics GP application and other applications that use the Microsoft Dynamics GP encryption process have the ability to translate the user’s password before sending it to Microsoft SQL Server. If a user tries to access the Microsoft SQL Server from outside the Microsoft Dynamics GP application, the attempt to log in will be denied because the passwords will not match. For improved security, Microsoft Dynamics GP does not allow a user to change their password to blank or unencrypted.
2.) DYNGRP database role A thorough understanding of the DYNGRP database role is vital to securing data. The DYNGRP database role is used to gain access to the objects, such as tables, stored procedures, and views that exist within the database. This simplifies the process of assigning specific permissions to the database objects. Granting SELECT, UPDATE, INSERT, DELETE, and EXECUTE permissions to the DYNGRP database for all objects that exist within the database eliminates the need to explicitly grant object access to individual users by SQL DBAs and the Microsoft Dynamics GP application. Instead, the Microsoft Dynamics GP individual users are members of the DYNGRP database, and those users inherit the same permissions. When an administrator grants a user access to a company within Microsoft Dynamics GP, the user also becomes a member of the DYNGRP for that corresponding database. While this database role is used in conjunction with the Microsoft Dynamics GP application, it is important to understand that only Microsoft Dynamics GP users should be members of this role. If user accounts that do not have encrypted passwords are placed inside this database role, users may have access via other applications. If other applications need access to Microsoft Dynamics GP data, the administrator should create new database roles with specific permissions established for only the objects that individual users need access to. Following this process reduces the risk that unauthorized users will gain access to your data.
3.) SysAdmin fixed server role There are two types of SQL Server roles: fixed server and database. Fixed server roles are used to manage SQL Server and perform functions that affect SQL Server availability and processes, such as creating databases and adding logins. Database roles are used to manage the specific database that the database roles are assigned to. Database management functions include granting user access, granting permissions to database objects, and backing up the database itself.
4.) SQL Server fixed database roles beginning with “rpt_” A Default SQL Server Fixed Database role that corresponds to each of the default SQL Server Reporting Services reports, data connections, and Microsoft Excel® reports is created during the installation of Microsoft Dynamics GP. Each role begins with “rpt_” and contains SELECT access to the Microsoft Dynamics GP data for the data connection or report that the role corresponds to. Users should be added as members to the SQL Server roles that correspond to the reports or data connections that they need access to. See your System Setup Guide (Help >> Contents >> select Setting up the System) for more information.