| Summary: | Information/support article about the database user permissions required to use Paribus Discovery and how to create a user wish such permissions in Microsoft SQL Server. |
| Article Type: | Technical Article |
| Related Product(s): | This article relates to the following products:
|
Overview
Paribus Discovery uses Microsoft SQL Server to host its Control Database(s). The Paribus Discovery Control Database is used to manage the match process and store the definitions and configurations created in the process of using Paribus Discovery.
While some Paribus users will only require a single Control Database to perform their tasks, others may require the ability to create new Control Databases. This is quite common when a new source of data is being “matched” against.
One solution to this problem is to simply give the user the ‘sa’ SQL Server login details; but this is both unnecessary and potentially destructive in terms of database management.
A better solution is to create a new SQL login which only gives the user access to do what is required for Paribus Discovery.
Creating a new SQL database login account
This section shows you how to create a new login account for a SQL Server database. This login can then be granted certain permissions, depending on the requirements of the Paribus user.
NOTE: If using a version of Paribus Discovery prior to 1.3.3; there can be an issue of Case-Sensitivity with SQL Server 2005.
Please refer to the related article “Paribus Discovery login problem with SQL Server 2005 (or later)” for more information.
Create a new database login
- Open the “Microsoft SQL Server Management Studio”
- In the Object Explorer (on the left-hand side):
- Expand the Security folder
- Expand the Logins folder
- Right-click on the Logins folder and select New Login…
(This will open the New Login window – Figure 1) - Type in the new SQL Server user’s login name
- Choose option “SQL Server Authentication”
- Type in the new user’s password (and confirm the password)
- (OPTIONAL) Set the password expiry options as required
- Click [OK] when finished.
This will create the new login account and list it under the Logins folder in the Object Explorer.
Granting new login to obtain list of databases
In order for Paribus Discovery to offer a list of Control Databases that the user can access, the new login needs to be granted a particular permission (View any definition).
- In the Object Explorer (on the left-hand side):
- Right-click on the Server name and click Properties
(Typically located at the top of the tree of folders)
This action will open the Server Properties window (Figure 2)
- On the left-hand side of the window, in the “Select a page” pane: click on Permissions
- Locate the newly created login from the list in the top pane, titled “Logins or roles”
The lower pane will now list the permissions that have currently been granted to the user’s login
- Scroll down the list of “Explicit permissions” for the new login and Grant (
) the “View any definition” permission as indicated in Figure 2 (below) - Click [OK]
Currently, the newly created SQL login is not very useful as it has very minimal permissions and has not been associated with any Paribus Discovery Control Databases.
The next couple of sections will guide you through configuring:
- access to Paribus Control Databases, and
- allowing creation of Paribus Control Databases
Granting access to Paribus Control Databases
In order for the new SQL account to be used to login to Paribus Discovery, the account must also be granted access to each Control Database that you want to use.
Grant permissions to access a Paribus Discovery Control Database
- In the Object Explorer (on the left-hand side):
- Expand the Databases folder
- Expand the database you want to give access to
(e.g. “ParibusControl”) - Expand the Security folder
- Expand the Users folder, which lists all the users that have been granted access to that database.
- Now, right-click on the Users folder and select New User…
This will open the New Database User window (Figure 3)
- Enter the User name, followed by the Login name (using the […] user name search tool, if necessary)
- In the “Role Members” list (located at the bottom part of this window): scroll down the list and make sure you select the “QG_ParibusUser” database role.
- Click [OK] to save changes
This new SQL account can now be used to login to the Paribus Discovery Control Database.
Allowing creation of Paribus Discovery Control Databases
If the Paribus Discovery user also requires the ability to create new Paribus Control Databases, follow the steps outlined below:
Grant permissions to create databases
- In the Object Explorer (on the left-hand side): locate the new account, which is under the Logins folder.
- Now right-click on the new user login and select Properties
This will open the Database User Properties window (Figure 4)
- On the left-hand side of the window, in the “Select a page” pane: click on Server Roles
This will show all the security roles that have (or could be) assigned to the new login account.
- Select the dbcreator Server role
Note: Choosing sysadmin will effectively grant this new login the same permissions as the “sa” login. - Click [OK] to save changes
This new SQL account can now be used in Paribus Discovery to create new Control Databases.
| Related Resources: | |
| Further Information: |