eNICQ 6 - How do I grant database permissions for a user?

Question: 

How do I grant database permissions for an eNICQ user or Active Directory group?

Answer: 

Please see the notes at the end of this article before starting this process. These are also the same steps to give someone the db_owner role to allow upgrades to the database.

  1. Open SQL Server® Management Studio.
  2. Locate the Security folder at the server level in the object explorer.
  3. Expand the Security Under the Security folder you will see the Logins folder.
  4. Right-click on the Logins folder and choose New Login…
    SQL Server Management Studio,Object Explorer,Menu after right-clicking on the Logins folder,Select
    If the users have already been given permissions to another database on this server they will show in Logins already. Just right click on them and select Properties and skip to step 19.

  5. At the bottom of the Login – New dialogue, set the Default database to eNICQ6db unless you named it something other than the default name. In that case select the actual name of the database.
    SQL Server Management Studio,bottom of Login-New screen,eNICQ 6 database selected as the default database

  6. Select the Windows authentication radio button.
  7. Click the Search button.
    SQL Server Management Studio,Login - New screen,Windows authentication selected,click Search button to proceed

  8. Click the Object Types button on the Select User or Group.
    SQL Server Management Studio,Select User or Group screen,Object Types... button

  9. Uncheck Built-in security principals and Other objects.
  10. Check Groups and/or Users, whichever you are using. For multiple users, managing through group membership is recommended.
    SQL Server Managemetn Studio,SSMS,Object Types Window,Select either Groups to grant access to database with Active Directory,or Users to grant access on a user by user basis

  11. Click OK to continue.
  12. On the Select User, Service Account or Group dialog Click Locations…
    SQL Server Management Stuido,SSMS,Select User,Service Accoutn,or Group,click Locations button

  13. Select the location for the Windows account or group and click OK.
    SQL Server Management Studio,SSMS,Locations window,select location where the user or group you are granting access is located

  14. Enter the user or group in the space provided and click Check Names.
    SQL Server Management Studio,SSMS,

  15. If you are unable to identify the correct user or group, click Advanced… on the Select User, Service Account or Group dialog for more search options. Otherwise, continue to step 19.
    SQL Server Management Studio,SSMS,

  16. Use the advanced features to find your user or group.
    SQL Server Management Studio,SSMS,

  17. Please contact your System Administration team for assistance if you have difficulty with any of the following:
    - Locating the correct user or group
    - Creating a user or group
    - Adding or removing users to or from a group
  18. On the Login - New screen, or the Login Properties screen if you are editing an existing user, click the User Mapping page.
  19. Check the eNICQ6db database in the upper panel if you kept the default name. If changed the name to something other than the default look for that in the upper panel and select it.
  20. Check the roles EnicqDBUser and public in the lower panel. You could also select the db_owner role here if you wanted to give someone the ALTER and CREATE permissions allowing them to perform database upgrades.

    SQL Server Management Studio,SSMS,Login - New window,User Mapping page selected,eNICQ6db database selected at the top,EnicqDBUser and public Database membership roles selected at the bottom

  21. Click OK to complete Login creation.
  22. Test the end-user’s database authentication capability from the client machine by having the end user launch the application once it has been installed.
  23. Set up SQL backups and secure your data according to your organization’s policies.

 

NOTES:

  • The eNICQ 6 application performs database transactions using the following isolation levels: RepeatableRead, ReadUncommitted, Snapshot, ReadCommitted. The SQL Server® database must allow usage of each of these isolation levels.
  • To perform the following actions, you will need to have SQL Server® Management Studio (SSMS) installed and connected with the instance of SQL Server® where the eNICQ 6 Database resides. If you do not already have a copy you can download the installer for SQL Server® Management Studio Express from Microsoft’s website, https://www.microsoft.com/en-us/download/details.aspx?id=43351.
  • The following instructions should be carried out in SQL Server® Management Studio by a database administrator or IT professional with equivalent skills and permissions. These instructions focus on using Windows Authentication to manage database access. Additional options and tips are available in our documentation on troubleshooting database connectivity in eNICQ 6.

 

Bethany Gray -