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 weekly.

Questions (February 23, 2001)
Answers (February 23, 2001)

Because next Wednesday (2/28/01) marks the end of the Windows NT 4.0 MCSE core exams, I've decided to take a break from core exam topics and explore some elective exam topics. I took my first Microsoft SQL Server course about 6 years ago; therefore, I thought I'd start covering electives with Exam 70-028, Administering Microsoft SQL Server 7.0, because this exam's predecessor was the first MCSE elective I took.

I've commented before that I think this exam is a good choice for an elective. SQL Server is becoming a common choice for backend data storage, especially for data-driven Web sites, which means that an MCSE will more than likely have a server running SQL Server somewhere in the company. In addition, the exam itself is very straightforward. The concepts are easy to understand, and the skills are easy to acquire with some hands-on exposure to SQL Server. Essentially, SQL Server does a good job of telling you when you do something wrong, and finding out how to do something the right way usually requires just reading the appropriate documentation section.

This week's questions cover basic server administration. My hope is that they will pique your interest in spending some time with my favorite Microsoft product.

Questions (February 23, 2001)

Question 1
Microsoft SQL Server and Microsoft Exchange Server are installed on the same machine, which has 256MB of physical RAM. Using Performance Monitor, you notice that during some time periods, SQL Server uses more than 200MB of physical memory, and during other periods, Exchange uses more than 220MB of physical memory. You also notice that often you must reboot the server to force one of the services to release memory.

During the periods when one service has more than 80 percent of the physical memory allocated, the performance of the other service deteriorates dramatically. You want to configure SQL Server so that it never has less than 64MB and never has more than 150MB of RAM allocated. Which set of settings will accomplish this?

  1. sp_configure 'min server memory', 32768
    sp_configure 'max server memory', 76800
  2. sp_configure 'min server memory', 64
    sp_configure 'max server memory', 150
    sp_configure 'set working set size', 1
  3. sp_configure 'min server memory', 64
    sp_configure 'max server memory', 150
  4. sp_configure 'set working set size', 32768, 76800

Question 2
You are the administrator of a Microsoft SQL Server 7.0 server that several different application development teams use. Each team member needs to be able to create and drop tables, indexes, and stored procedures in the team's database. Because of the sensitive nature of the data in some of the databases, you want to retain control over object access permissions, user access to the database, and role membership. Which fixed database role should you use for the team members?

  1. db_owner
  2. db_accessadmin
  3. db_securityadmin
  4. db_ddladmin
  5. db_dumpoperator

Question 3
You are the Database Owner (DBO) and create a view on the table dbo.customers. John then creates a stored procedure that uses the view during its operation. John grants Lisa execute permission on the stored procedure.

Your Microsoft SQL Server login account is a member of the sysadmins fixed server role. John's Windows NT account is a member of the \[Database Owners\] local group, and \[Database Owners\] is a member of the db_owner database role. Lisa's database user account is a member of the Public database role only. Only the db_owner role has SELECT permission on both the view and the table it uses.

When Lisa runs the stored procedure, what result will she receive?

  1. The stored procedure will run correctly.
  2. The stored procedure won't execute, and SQL Server will return an error message.
  3. The stored procedure will fail because Lisa doesn't have SELECT permission on the view.
  4. The stored procedure will fail because Lisa doesn't have SELECT permission on the table that the view uses.

Answers (February 23, 2001)

Answer to Question 1
The correct answer is C—sp_configure 'min server memory', 64; sp_configure 'max server memory', 150. By default, SQL Server allocates memory for caching data dynamically based on the available physical RAM. Usually, SQL Server allocates RAM as needed until physical RAM is in the range of 4.8MB to 5.2MB. When available RAM falls below 4.8MB, SQL Server releases memory back to the system.

When SQL Server is running on a server with an application that competes for memory, SQL Server's dynamic allocation can cause problems as applications allocate and release memory, especially because that process requires memory to be written out to the paging file. In the scenario in the question, competing with Exchange keeps SQL Server from performing well. Answer C shows the command that tells SQL Server to make sure it never gets less than 64MB and never allocates more than 150MB.

Answer to Question 2
The correct answer is D—db_ddladmin. SQL Server 7.0 has several built-in database roles that have preassigned permissions. Members in the db_ddladmin role can use all the data definition language statements, including CREATE and DROP, but they can't assign permissions to those objects using the GRANT, DENY, and REVOKE statements.

Answer to Question 3
The correct answer is C—The stored procedure will fail because Lisa doesn't have SELECT permission on the view. This scenario is an example of a broken ownership chain. When a view or stored procedure uses objects that another account owns, SQL Server checks the permission assigned to each object referenced. In this scenario, Lisa has permission to execute the stored procedure, but she doesn't have SELECT permission on the view. For Lisa to execute the stored procedure properly, you, as the owner of the view and the table, must grant her SELECT permission on the view.

You can resolve this problem by having the same account, usually dbo, own all objects. When the same account owns all referenced objects, SQL Server checks only the permission for the outermost referenced object instead of checking all objects. For a description of ownership chains, see "Ownership Chains" in SQL Server Books Online.