Certifiable Q&A for January 12, 2001

Welcome to Certifiable, your exam prep headquarters. Here you'll find questions about some of the tricky areas that are fair game for the certification exams. Following the questions, you'll find the correct answers and explanatory text. We change the questions biweekly.

Questions (January 12, 2001)
Answers (January 12, 2001)

When my students ask which electives they should take, I always tell them to take everything related to TCP/IP. The truth beneath all the hype about the Internet is that TCP/IP now permeates the entire computing landscape; therefore, anyone who works in the IT field today must know at least something about how TCP/IP networks function. As far as I'm concerned, that applies to MCSE, MCSD, and MCDBA candidates alike.

Another important trend that has emerged in the past couple of years is that companies are acquiring large quantities of information about their customers, their production processes, and their businesses as a whole. All that data must be stored somewhere, and SQL Server has increasingly become the repository of choice; therefore, I recommend that nearly every certification candidate have at least a minimal understanding of how SQL Server works.

As it turns out, Exam 70-028: Administering Microsoft SQL Server 7.0, is straightforward. The topics it covers relate directly to the tasks that all system administrators face almost every day, and the questions are easy to interpret. I'm not suggesting that it's an easy exam. It just happens to be one where the concepts are easy to understand because they're logical. (What do you expect from an exam about a database?)

The added incentive for taking Exam 70-028 is that it counts toward the MCSE, MCSD, and MCDBA certifications. The following questions cover some important topics on the exam, which I hope many of you will consider taking as your second elective.

Questions (January 12, 2001)

Question 1
Your company runs 100 call centers that manage reservations for an airline. The reservation application maintains a customer table on the corporate server. The customer table is Read Locally. You can update the table locally, but the updates must maintain consistency across all 100 locations. Which type of SQL Server replication should you implement?

1. Merge replication with push subscribers.
2. Merge replication with pull subscribers.
3. Transaction replication with push subscribers.
4. Transaction replication with immediate-updating subscribers.

Question 2
Running SQL Server 7.0, you issue the following commands:

Create Table Table1 (
    ID int,
    Description varchar(8000),
    ItemImage image)
on default
textimage_on default

What's the effect?

1. The statement creates Table1 in the default database and stores text and image data on the default file group.
2. The statement creates Table1 in the default database, places the table's data on the default file group, and places the Description and ItemImage columns in the default file group reserved for text and image data.
3. The statement creates Table1 in the current database, places the ID and Description columns on the default file group, and places the ItemImage column on the default file group reserved for text and image data.
4. The statement creates Table1 in the current database and stores all data on the default file group.

Question 3
Your company's payroll manager asks you to set up security for the ACCOUNTING and PAYROLL databases. You have the following security requirements:

• The payroll manager must have SELECT, INSERT, UPDATE, and DELETE permissions on all tables in the PAYROLL database.
• The payroll manager must have SELECT permission on all payroll records in the ACCOUNTING database.
• The payroll manager must not be able to modify records in the ACCOUNTING database.
• The payroll manager must access both databases through the payroll application only.
• You must be able to trace all operations in both databases to the specific user.

You set the following permissions on the ACCOUNTING and PAYROLL databases:

• In the ACCOUNTING database, you grant the Public role SELECT permissions on the PAYROLL_RECS view.
• In the ACCOUNTING database, you create the Guest user account.
• In the PAYROLL database, you revoke all permissions for the Public role.
• In the PAYROLL database, you grant the PAYROLL_ADMINS role SELECT, INSERT, UPDATE, and DELETE permissions.
• You make the payroll manager's login account a member of the PAYROLL_ADMINS role in the PAYROLL database.

This solution meets which of the following requirements?

1. The payroll manager has SELECT, INSERT, UPDATE, and DELETE permissions on all tables in the PAYROLL database.
2. The payroll manager has SELECT permissions on all payroll records in the ACCOUNTING database.
3. The payroll manager can't modify records in the ACCOUNTING database.
4. The payroll manager can access both databases through the payroll application only.
5. You can trace all operations to a specific user.

Answers (January 12, 2001)

Answer to Question 1
The correct answer is D—Transaction replication with immediate-updating subscribers. The immediate-updating subscribers option provides latent transactional consistency to other subscribers (it occurs immediately between the subscriber performing the update and the publisher) without requiring that updates occur at the publishing site only. Because the subscriber performing the update already has the data changes reflected locally, the user can continue to work with the updated data secure in the knowledge that the publisher data also reflects the change. No loss of transactional integrity occurs.

Answer to Question 2
The correct answer is D—The statement creates Table1 in the current database and stores all data on the default file group. Although default file groups aren't practical in many situations, one of the main exam topics is data storage administration, of which the default file group behavior is a significant part. Not every user has a default database, but the CREATE TABLE statement always creates the table in the current database unless the statement has a fully qualified name that includes both the database and the object owner. SQL Server 6.5 stores image and text data type columns separately from other data types, but SQL Server 7.0 stores all data types in the same database file. This particular statement places all data in the default file group for the current database. See "Physical Database Architecture" in SQL Server 7.0 Books Online for more information about files and file groups.

Answer to Question 3

The correct answer is A—The payroll manager has SELECT, INSERT, UPDATE, and DELETE permissions on all tables in the PAYROLL database; B—The payroll manager has SELECT permissions on all payroll records in the ACCOUNTING database; and C— The payroll manager can't modify records in the ACCOUNTING database.

Granting SELECT permission to the Public role grants the right to execute SELECT statements to anyone who uses the database. Like the Everyone local group in Windows NT 4.0, all database users, including Guest users, are always members of the Public role. Similarly, all members of a user-defined role receive the permissions granted to the role; therefore, being a member of the PAYROLL_ADMINS role grants SELECT, INSERT, UPDATE, and DELETE permissions in the PAYROLL database.

Database permissions can't force user access to data through an application only. To do that, you must use the application role (for more information, see "Application Security and Application Roles" in SQL Server 7.0 Books Online). If you enable the Guest account in the ACCOUNTING database, you have no way to trace operations to a specific user, although you can still trace users to a specific login.