SmartConnect 2018
GP Database Security
SmartConnect users are based on, and receive their permissions from Windows AD users. As GP user permissions are separate from AD user permissions, permission to access and update GP databases must be added to MSSQL for SmartConnect to operate correctly.
From SmartConnect 2018 administrators will be able to define a single MSSQL user which SmartConnect should use to connect to the Dynamics GP Company databases.
To define a single user with which to connect to Dynamics GP databases:
| 1. | Open Setup >> SmartConnect Setup. |
| 2. | Double click on the SmartConnect Dynamics GP Connector, or select SmartConnect Dynamics GP Connector and select the Connector Setup button at the bottom of the screen. This will open the Microsoft Dynamics GP Connector Setup window. |
| 3. | Select the use credentials checkbox |
| 4. | Enter the name of the MSSQL user that should be used when connecting to Dynamics GP company databases. |
| 5. | Enter the password for the above user. |
| 6. | Enter the name of the Dynamics GP database server, then select the connect button. |
| 7. | Select the default Dynamics GP company. |
| 8. | Select Save to record changes. |
To add a Windows AD users to the GP databases use the script below:
| 1. | In the script below, make the following replacements |
| a. | Replace domain\username with the login details for each user. |
| b. | If the system database is not called DYNAMICS, replace DYNAMICS with the name of the GP system database. |
IF NOT EXISTS (SELECT loginname FROM master.dbo.syslogins WHERE name = 'domain\username')
BEGIN
CREATE LOGIN [domain\username] FROM WINDOWS WITH DEFAULT_DATABASE=master, DEFAULT_LANGUAGE=[us_english]
| USE DYNAMICS |
| CREATE USER [domain\username] FOR LOGIN [domain\username] WITH DEFAULT_SCHEMA=[dbo] |
| EXEC sys.sp_addrolemember 'DYNGRP',[domain\username] |
END
GO
| 2. | Run the edited script from step 1. |
| 3. | Duplicate the lines below for each GP company database the user should be able to access and make the following replacements |
| a. | Replace domain\username with the login details for each user. |
| b. | Replace TWO with the name of the Microsoft Dynamics GP company database you want to give them access to. |
USE TWO
CREATE USER [domain\username] FOR LOGIN [domain\username] WITH DEFAULT_SCHEMA=[dbo]
EXEC sys.sp_addrolemember 'DYNGRP',[domain\username]
| 4. | Run the edited scripts from step 3. |
| 5. | Alternatively an AD user group may be added to MSSQL, the appropriate permissions added, then the AD users added to the AD user group. |