Creating a SQL Server Login

This is a Technical (IT) Instructions topic

Introduction

This article applies to MS SQL Server 2008. It explains how to create a SQL Server login for the AquilaServer application server to connect to the Aquila database.

The user login will be created using SQL Server Authentication, not Windows Authentication. This allows us to create a login that exists only in the context of the database server.

Step-by-Step

1. Open SQL Server Management Studio.
2. Connect to the SQL Server database where you would like to create a login.
3. Open the Security folder.
4. Right-click on the Logins folder and select New Login.
5. Select "SQL Server authentication".
6. Provide the login name in the text box. The Browse button will be disabled as we are not creating a Windows Authentication account.
7. Provide a password in both the Password and Confirmation text boxes.
8. Change the default database for the account to point to your Aquila/IBID database using the drop-down boxes at the bottom of the window.
9. Add database permissions to the account (see #Adding database permissions to the login)
10. Click OK to create the account.

Adding database permissions to the login

1. If you do not have the login properties open, locate the login name in the Security folder, right click and choose properties
2. Click on the User Mapping page
3. In the section Users mapped to this login, tick the box next to your Aquila/IBID database
4. Underneath, in the section Database role membership for <database>, tick the following roles:
   1. db_backupoperator - allows you to use this user to take backups of the database
   2. db_datareader - allows SELECT queries against the database
   3. db_datawriter - allows INSERT, UPDATE and DELETE queries against the database
   4. db_ddladmin - allows this user to make modifications to database objects
   5. public - ensures inherited rights from the public group
5. Click OK to save the account.