Microsoft Dynamics GP uses security features provided by SQL Server to help maintain security of data in the accounting system. Each user added to Microsoft Dynamics GP is also a SQL user. To maintain security, it’s important to grant only the SQL access privileges that are necessary for specific user to perform tasks in the accounting system.
Some actions you need to perform for your integration, such as creating tables, must be performed by a user that has sufficient SQL privileges. You might consider performing these actions while running Microsoft Dynamics GP as “sa”, the built-in SQL Server administrator user. We discourage using the “sa” user for this purpose. The “sa” user has unlimited access privileges to the SQL server, and should be used sparingly to help keep the system secure.
Instead, we suggest using the “DYNSA” user when peforming administrator actions like creating tables and stored procedures. The “DYNSA” user is created during the Microsoft Dynamics GP installation just for this purpose. It has sufficient SQL privileges to perform administrative actions like creating tables, but only in databases used by the Microsoft Dynamics GP application. Limiting the access of the “DYNSA” user helps keep the SQL server more secure.
You may want to code your integrating application so that administrative tasks can be performed properly when running as either the “DYNSA” or “sa” user. Refer to Creating SQL tables for more information about doing this.
You might also choose to use the syUserInRole() function to determine what SQL Server privileges the current user has. If the user is in a SQL Server role that has the necessary privileges to perform the SQL task, such as creating tables, you can allow the operation to proceed. Otherwise, your application should prevent the user from accessing the functionality, or display an appropriate error message indicating the lack of privileges.