Creating a user on the primary External Data Mart SQL Server

Last Updated : Aug 14, 2023 |

About this task

Use this procedure to create an optional non-system administrator user on the primary External Data Mart (EDM) SQL Server.

Procedure

  1. Log in to the primary EDM SQL Server.
  2. Click Start > Microsoft SQL Server > Open SQL Server Management Studio.
  3. In the Object Explorer pane, click Connect > Database Engine.

    SQL Server Management Studio displays the Connect to Server dialog box.

  4. In the Server name field, select the local instance of SQL Server.
  5. In the Authentication field, select SQL Server Authentication.
  6. In the Login and Password fields, enter the system administrator credentials.

    The system administrator is usually the default sa user created during SQL Server installation.

  7. Click Connect.
  8. In the Object Explorer pane, right-click the SQL Server instance and click New Query.
  9. In the content pane, run the following command to create a new login: 
    USE [master]
GO
CREATE LOGIN <<loginName>> WITH PASSWORD = '<<password>>', CHECK_POLICY=OFF

    For example:

    USE [master]
GO
CREATE LOGIN csEDMLogin WITH PASSWORD = 'myPassword1', CHECK_POLICY=OFF
  10. Run the following command to select the security identifier (SID) for the new login: 
    SELECT sid, name FROM syslogins where name = '<<loginName>>'

    For example:

    SELECT sid, name FROM syslogins where name = 'csEDMLogin' 
shows SID = 0x0589627E7A96D447865135449C41E53B
    Important:

    SID is used when creating the user if using a pair of SQL Servers for database high availability.

  11. In the Object Explorer pane, click Security > Logins.
  12. Right-click the newly created login name and click Properties.

    SQL Server Management Studio displays the Login Properties dialog box.

  13. In the navigation pane, click General.
  14. In the Default database field, select the name for the EDM database.
  15. In the navigation pane, click User Mapping.
  16. Select the Map check box for the EDM database.
  17. Click OK.
  18. In the Object Explorer pane, click Databases > <EDM Database> > Security > Users.
  19. Verify that the newly created login name is available in the list of users.
  20. In the Object Explorer pane, right-click the EDM database and click New Query.
  21. In the content pane, run the following commands to grant permissions to the newly created user: 
    GRANT SELECT, INSERT, UPDATE, DELETE ON CS_OPERATION TO <<loginName>>;
GRANT SELECT, INSERT, UPDATE ON CS_PROVISION TO <<loginName>>;
GRANT SELECT, INSERT, UPDATE ON GENERATED_KEYS TO <<loginName>>;
GRANT SELECT, INSERT, UPDATE ON INTERACTION_NOTE to <<loginName>>;
GRANT SELECT, INSERT, UPDATE ON INTERACTION_PARTICIPANT to <<loginName>>;
GRANT SELECT, INSERT, UPDATE ON SERVICE_ATTRIBUTE to <<loginName>>;
GRANT SELECT, INSERT, UPDATE ON SERVICE_ATTRIBUTE_GROUP to <<loginName>>;
GRANT SELECT, INSERT, UPDATE ON ROUTING_SERVICE to <<loginName>>;
GRANT SELECT, INSERT, UPDATE ON CUSTOM_DETAIL to <<loginName>>;
GRANT SELECT, INSERT, UPDATE ON INTERACTION to <<loginName>>;
GRANT SELECT, INSERT, UPDATE ON LINK to <<loginName>>;
GRANT SELECT, INSERT, UPDATE ON TOPIC to <<loginName>>;
GRANT SELECT, INSERT, UPDATE, DELETE ON ENGAGEMENT to <<loginName>>;
GRANT SELECT, INSERT, UPDATE ON OUTCOME to <<loginName>>;
GRANT SELECT, INSERT, UPDATE ON SENTIMENT_RATING to <<loginName>>;
GRANT SELECT, INSERT, UPDATE ON TENANT to <<loginName>>;
GRANT SELECT, INSERT, UPDATE ON INTERACTION_STATUS to <<loginName>>;
GRANT SELECT, INSERT, UPDATE ON INTERACTION_SUBSTATUS to <<loginName>>;
GRANT SELECT, INSERT, UPDATE ON CHANNEL_SUBTYPE to <<loginName>>;
GRANT SELECT, INSERT, UPDATE ON CHANNEL_TYPE to <<loginName>>;
GRANT SELECT, INSERT, UPDATE, DELETE ON CS_RESURRECT to <<loginName>>;