Creating SQL Login Permissions

Creating SQL Logins & Permissions

Setting up a Shared SQL Server Instance

If you’re the only PHDwin user and you installed a local instance of SQL Server where you’re the system administrator, you’re all set! You don’t need to do anything else. However, if you’re part of an IT team installing PHDwin and SQL Server for multiple users, you’ll need to create a SQL login for each PHDwin user. Additionally, if SQL Server was installed on a shared network location for multiple users to access, you’ll need to enable TCP/IP & Named Pipes connections. Note that all PHDwin users must be using the same build number of PHDwin in order to access a shared SQL Server instance.

Adding a Login to SQL Server

To save time managing permissions for individual users, it is recommended to create a domain group that includes all PHDwin users if multiple users will be logging into a shared SQL Server Instance for PHDwin.
Each domain group or individual PHDwin user must be added as a login for the SQL Server using SQL Server Management Studio.

By creating a domain group and adding it as a login, you can easily manage the permissions for all PHDwin users at once instead of having to add each user individually. This can be especially helpful for large teams or organizations with multiple users who need access to PHDwin.

To Add a New Login

Open SQL Server Management Studio and connect to the instance you’ll use to store PHDwin databases. The server must be a part of the domain that you created the group on.
Expand Security to find the Logins – right-click on Logins and select Add New.
Click on the Search button next to the Login name to search for the group that you created. This will open a new window. a. Click on Object Types and make sure that Group or User is selected. b. Make sure the location underneath “From this location” is set correctly. You may need to click on the Locations button to choose the network that the group/individual is a part of. c. In the section that reads “Enter the object names”, type in the name of the group or use and click Check Names. d. The name you were searching for should show up with the text underlined. Once you see this name, click OK to close the window.
Underneath the login name, you will choose the authentication method – Windows authentication or SQL Server authentication which allows you to specify a password for the login. a. If you select Windows authentication for a group login, the domain will know which users belong to any groups you’ve created and allow them to log in with the permissions for the group. b. If you select SQL Server Authentication, you must create a Username and Password for the PHDwin user. Uncheck “User must change password at next login” as this action cannot be performed from the PHDwin login screen.
Go to the Server Roles page.
- By default, the login will have a public server role.
- If you are creating a login for a PHDwin user on their local instance, click the box for the sysadmin server role. This will enable them to create databases in PHDwin, make changes, and delete them if necessary.
- If you do not want to make the user a sysadmin, view the section below on limiting SQL permissions.
Click OK to save your changes.

To set the minimum SQL login permissions for each user or domain group accessing PHDwin on a shared SQL Server instance

Open SQL Server Management Studio and connect to the instance where PHDwin databases will be stored. The server must be a part of the domain that you created the group on.
2. Expand the Security section to find the Logins.
3. Right-click on the login that will be assigned permissions and select Properties.
4. In the Login Properties window, go to the Server Roles page.
5. Under the list of Server Roles, click the box next to public and sysadmin like this:

Click OK.

Click OK to save your changes.

Repeat these steps for each user.

Enable TCP/IP and Named Pipes

If you have installed SQL Server on a network server and need to allow other users to connect to it, you must enable TCP/IP and Named Pipes connections. These connections allow other devices to communicate with the server and access its databases.

To Enable TCP/IP and Named Pipes

Open SQL Server Configuration Manager. You can search for this application in the Windows start menu.
In the console pane, expand SQL Server Network Configuration.
Click on Protocols for <instance_name> in the console pane.
In the details pane, right-click on TCP/IP and select Enable.
In the details pane, right-click on Named Pipes and select Enable.
Click on SQL Server Services in the console pane.
In the details pane, right-click on SQL Server (<instance_name>) and select Restart to stop and restart the SQL Server service to apply the changes.

Setting up a Group on the Domain

To create a domain group that contains all PHDwin users, follow these steps:
1. Log in to the server with the domain controller – the instance of SQL server used by PHDwin must have access to this domain controller.
2. Open the Active Directory Users & Computers by navigating to Administrative Tools > Active Directory Users and Computers.
3. Find the domain where you would like to add the group and expand it to find the Users grouping. Right-click on Users and select New > Group.
4. Give the group a Name and select the Group scope and group type. The group type should be “Security,” and the group scope will typically be “Global,” but this may depend on the rules and requirements of your company. Click OK to save the group.
5. Find the group that was created in the list of Users, right-click on it and choose Properties.
6. Click on the Members tab and then click on the Add button to add the first user to the group. a. Under “Select this object type,” click on Object Types, and make sure “Users” is selected. b. Under “From this location,” make sure you are on the domain that you’d like to add users from. Click on the Locations button to change this. c. Type in all or part of the name of the first user that you’d like to add and click on Check names. The username should pop up, select it, and hit OK. d. Repeat this process for all the PHDwin users that you’d like to be a part of this group.
7. Once you are finished adding users, click OK to save your changes.
By creating a domain group that contains all PHDwin users, you can save time managing permissions for individual users. More information on domain groups, including settings and adding or removing members, can be found on Microsoft’s website.