Contents

Run SQL Server script to add AD accounts

Previously, you created the domain user and group accounts that use specific permissions in SQL Server. Next, you run a set of SQL scripts to add the AD accounts to SQL Server by using the SQL Server Admin Console. The SQL scripts add the AD accounts and the SQL roles in the SQL Server application. This step allows the appropriate AD accounts access to the CIC database to perform read, write, and management operations. The following illustration shows an example script.

Run the script:

Log on to the Microsoft SQL Server Management Studio console by using the local domain ICDatabase user account or local equivalent.
Select New Query from the toolbar.
Set the focus to the master database so that the actions are replicated in the master database and other databases on this SQL server.
Drop login accounts. This part of the script removes the SA created SQL accounts on the SQL Server. To drop login accounts, you copy and paste a line of the script (for example, DROP LOGIN [IC_Admin]) into the right pane of the Query window and select Execute from the toolbar to execute the line.

Copy, paste, and execute each of the following lines of the script:
```
         DROP LOGIN [IC_Admin]
         DROP LOGIN [IC_User]
         DROP LOGIN [IC_ReadOnly]
      
```
Note:
When you execute these lines, you may receive a warning or error that the user is connected. This is expected behavior. You can continue with the next step.
Add domain users to the database. This part of the script adds the following domain users that you created earlier. Refer to the “Create domain user accounts” section in this document for more information about these accounts.
```
         Domain\DB_IC_Admin
         Domain\DB_IC_ReadOnly
         Domain\DB_IC_User
      
```
To add domain users to the database, copy and paste a line of the script (for example, CREATE LOGIN [cnetininDB_IC_Admin] FROM WINDOWS) into the right pane of the Query window and select Execute from the toolbar to execute the line.

Copy, paste, and execute each of following lines of the example script. The lines from the example script use the domain name of cnetinin. Replace cnetinin with your domain user name before you execute the line.
```
         CREATE LOGIN [cnetinin\DB_IC_Admin] 
 
 
 FROM WINDOWS
         CREATE LOGIN [cnetinin\DB_IC_User] 
 
 
 FROM WINDOWS
         CREATE LOGIN [cnetinin\DB_IC_ReadOnly] 
 
 
 FROM WINDOWS
      
```
Add domain groups to the database. This part of the script adds the following domain groups that you created earlier. Refer to the “Create domain group accounts” section in this document for more information about these accounts.
```
         Domain\ICDataOwner
         Domain\ICDataReader
         Domain\ICDataWriter
         Domain\ICDBAdmins
      
```
To add domain groups to the database, copy and paste a line of the script (for example, CREATE LOGIN [cnetinin\ICDataOwner] FROM WINDOWS) into the right pane of the Query window and select Execute from the toolbar to execute the line.

Copy, paste, and execute the following lines from the example script. The lines from the example script use the domain name of cnetinin. Replace cnetinin with your domain group name before you execute the line.
```
         CREATE LOGIN [cnetinin\ICDataOwner] 
 
 
 FROM WINDOWS
         CREATE LOGIN [cnetinin\ 
 
 
 ICDataReader] FROM WINDOWS
         CREATE LOGIN [cnetinin\ 
 
 
 ICDataWriter] FROM WINDOWS
         CREATE LOGIN [cnetinin\ 
 
 
 ICDBAdmins] FROM WINDOWS
      
```
Set the focus to the CIC database. The example script uses the I3_IC_gh1 database. Set the focus to your CIC database name.
Create user logins for domain groups. This part of the script creates user logins for the following domain groups that you created earlier. Refer to the Create domain group accounts section in this document for more information about these accounts.
```
         Domain\ICDataOwner
         Domain\ 
 
 
 ICDataReader
         Domain\ 
 
 
 ICDataWriter
         Domain\ 
 
 
 ICDBAdmins
      
```
To create user logins for domain groups, copy and paste a line of the script (for example, CREATE USER [cnetininICDataOwner] FOR LOGIN [cnetininICDataOwner]) into the right pane of the Query window and select Execute from the toolbar to execute the line.

Copy, paste, and execute the following lines from the example script. The lines from the example script use the domain name of cnetinin. Replace cnetinin with your domain group name before you execute the line.
```
         CREATE USER [cnetinin\ICDataOwner] 
 
 
 FOR LOGIN [cnetinin\ICDataOwner]
         CREATE USER [cnetinin\ICDataReader] 
 
 
 FOR LOGIN [cnetinin\ICDataReader]
         CREATE USER [cnetinin\ICDataWriter] 
 
 
 FOR LOGIN [cnetinin\ICDataWriter]
         CREATE USER [cnetinin\ICDBAdmins] 
 
 
 FOR LOGIN [cnetinin\ ICDBAdmins]
      
```

Alter roles and assign groups to roles. This part of the script alters SQL roles and assigns groups to roles for the following domain groups that you created earlier. Refer to the Create domain group accounts section in this document for more information about these accounts.

         Domain\ICDataOwner
         Domain\ 
 
 
 ICDataReader
         Domain\ 
 
 
 ICDataWriter

To alter roles and assign groups to roles, copy and paste a line of the script (for example, ALTER ROLE [db_datareader] ADD MEMBER [cnetininICDataOwner]) into the right pane of the Query window and select Execute from the toolbar to execute the line.

Copy, paste, and execute the following lines from the example script. The lines from the example script use the domain name of cnetinin. Replace cnetinin with your domain group name before you execute the line.

         ALTER ROLE [db_datareader] 
 
 
 ADD MEMBER [cnetinin\ICDataOwner]
         ALTER ROLE [db_datawriter] 
 
 
 ADD MEMBER [cnetinin\ICDataOwner]
         ALTER ROLE [EIC_READ] 
 
 
 ADD MEMBER [cnetinin\ICDataOwner]
         ALTER ROLE [db_datareader] 
 
 
 ADD MEMBER [cnetinin\ICDataWriter]
         ALTER ROLE [db_datawriter] 
 
 
 ADD MEMBER [cnetinin\ICDataWriter]
         ALTER ROLE [EIC_READ] 
 
 
 ADD MEMBER [cnetinin\ICDataWriter]
         ALTER ROLE [db_datareader] 
 
 
 ADD MEMBER [cnetinin\ICDataReader]
         ALTER ROLE [EIC_READ] 
 
 
 ADD MEMBER [cnetinin\ICDataReader]