To Create Custom Database Roles in SQL Server 2000, Perform the Following Steps

Lab Procedures:

Unit 8, Ex – 1

To create custom database roles in SQL Server 2000, perform the following steps:

1. Click StartProgramsMicrosoft SQL ServerEnterprise Manager.

2. Select the model database from the Server root, as shown in the Figure.

Selecting model Database

3. Expand model database, and then select Roles, as shown in the Figure.

Database Roles

4. On the Action menu, click New Database Role, as shown in the Figure.

New Database Role Option

5. The Database Role Properties dialog box appears. Enter the name and select the Standard role as database role type, as shown in the Figure.

Database Role Properties Dialog Box

6. Click Add. Select the user type from the Add Role Members dialog box that appears, as shown in the Figure. Click OK.

Add Role Members Dialog Box

7. Click OK. The new user is updated in the user list, as shown in the Figure.

Roles with the New User

8. Right-click the new user, and then select Properties. The Database Role Properties–username dialog box appears, as shown in the Figure.

User Database Role Properties Dialog Box

9. Click Permissions. In the Database role drop-down list, click the database role created in step 7. Select the SELECT check box to select the permission, as shown in

the Figure.

Click OK to close the Database Role Properties dialog box.

Permissions Window

Unit 8, Ex – 2

To create an application role, perform the following steps:

1. Click StartProgramsMicrosoft SQL ServerEnterprise Manager.

2. Select the model database from the SQL Server Databases root, as shown in the Figure:

Selecting the model Database

3. On the Action menu, click the New Database Role option from the Action menu, as shown in the Figure:

New Database Role Option

4. Select the Application role option, and enter the password on this screen, as shown in the Figure:

Database Role Properties - New Role Dialog Box

5. Click OK to return to the Enterprise Manager console.

6. Click Roles. Right-click UserName from the right pane, and then select Properties from the pop-up menu that appears.

7. The Database Role Properties—New Role dialog box appears again. Click Permissions.

8. The Database Role Properties—model dialog box appears, as shown in the Figure:

Database Role Properties Dialog Box

9. Select the SELECT check box to grant this permission, as shown in the Figure:

Database Role Properties Dialog Box

Then, click OK to close the dialog boxes and complete the process.

Unit 8, Ex – 3

To create a new user in a group in SQL Server 2000, perform the following steps:

1. Click StartProgramsAdministrative ToolsActive Directory Users and Computers group. The Active Directory Users and Computers window appears, asshown in the Figure:

Active Directory Users and Computers Window

2. Select the workgroup domain, and then click New#User from the Action menu, as shown in the Figure:

Creating a New User

3. Double-click the New User and enter the first name, initials, last name, and user logon name in the New Object - User dialog box, as shown in Figure:

New Object - User Dialog Box

In the Figure above, you can replace the samples with appropriate user, logon, workgroup, and domain names, such as Mary Smith, msmith, Sales, and abcinc, respectively.

4. Click Next to move to the next screen. Enter the password in the Password and

Confirm password text boxes, as shown in the Figure.

Note: Leave the four check boxes clear.

New Object–User Dialog Box

5. Click Next, and then click Finish, as shown in the Figure:

New Object - User

Restart the SQL Server. Log on as the new user to obtain the username and login information.

The newly created user is displayed in the Users pane, as shown in the Figure:

Users Window

Unit 8, Ex – 4

To check or set object permissions in SQL Server 2000, perform the following steps:

1. Click StartProgramsMicrosoft SQL ServerEnterprise Manager.

2. Select the model database from the SQL Server Group databases root.

3. Select the Tables database, as shown in the Figure:

Selecting Tables Database

4. Right-click syscolumns, and select Properties, as shown in the Figure.

Properties Window

5. The Table Properties dialog box appears, as shown in the Figure.

Table Properties Dialog Box

Click Permissions.

6. Select the object from the Object drop-down list, select List all users/user-defined database roles/public, and then grant permission for SELECT, as shown in the Figure.

Object Properties Window

Click OK to close both the dialog boxes.

Conclusion/Observation

You learned to check permissions for a table.

1

Lab Handout for IT390

Business Database Administration