MSCE Interview Questions and Answers

QUESTION 101
You work as the database administrator at Certkiller .com. The Certkiller .com network consists of a single Active Directory domain named Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server and all client computers run Windows 2000 Professional.
You are planning to implement SQL Server 2000 in Certkiller .com. There are three departments that will require separate tables to store their information. These departments are Finance, Human Resources and Sales respectively.
1. The Finance department users belong to a Windows 2000 group named Fin.
2. The Human Resources department users belong to a Windows 2000 group named HR.
3. The Sales department users belong to a Windows 2000 group named Sales.
Each of these departments' information must be protected. Users in the respective department will require full access to the data in their corresponding department; they should not have access to the other departments' data. You need to ensure that your solution requires the minimum system resources and administrative overhead.
What should you do?

A. An instance of SQL Server must be installed on a separate computer for each department. And a separate database must be created for each department.
B. A separate instance of SQL Server must be installed for each department on the same computer. And a separate database must be created for each department.
C. A single instance of SQL Server must be installed for all departments. And a separate
database must be created for each department.
D. A single instance of SQL Server must be installed for all departments. And a single
database must be created for all departments.

Answer: C

Explanation: SQL server is capable of supporting 16 instances on a single computer. But
multiple instances drains the system resources. With a single instance of SQL Server, you
are allowed to perform routine database maintenance on a single computer. Separate
databases within the single instance will reduce administrative overhead that is related to
security. Instead of monitoring which table or column in the same database must be
restricted to the members of a particular department, you are able to assign permissions
to departmental members for their respective tables or columns only. Thus you should
install a single instance of SQL Server and then create a database for each department
respectively.

Incorrect answers:

administrative overhead.


QUESTION 102

You work as the database administrator at Certkiller .com. Certkiller .com has five departments named Accounting, Sales, Marketing, Research and Manufacturing.The Certkiller .com user accounts of each department are located in a domain groupnamed after the department. The Certkiller .com network consists of a single ActiveDirectory domain named Certkiller .com. All servers on the Certkiller .com networkrun on Windows Server 2000. The Certkiller .com network contains a SQL Server2000 database server named Certkiller -DB01.You have been instructed to create a separate database on Certkiller -DB01 for each department. Each Certkiller .com department will use their database to store department-related data. Users in each department must be able to read and updatedata in the database belonging to their department. No users must be able to accessthe database belonging to another department. You need to configure access to the databases to meet these requirements using the least amount of administrative effort.
What should you do?

A. Create a Windows Authentication login for each domain group and configure the logins as database users for the appropriate database. Add each database user to the db_datareader and db_datawriter database roles.
B. Create a Windows Authentication login for each domain user and configure the logins as database users for the appropriate database. Add each database user to the db_datareader and db_datawriter database roles.
C. Create a Windows Authentication login for each domain group and configure the logins as database users for the appropriate database. Add each database user to the db_ddladmins database role.
D. Create a Windows Authentication login for each domain user and configure the logins as database users for the appropriate database. Add each database user to thedb_ddladmins database role.

Answer: A

Explanation: The users of each department belong to a group named after the
department. You can use these groups to configure access to the databases. Each database user should be added to the db_datareader and db_datawriter database roles for their respective databases. This will prevent users in one department from accessing another department's database.

Incorrect answers:

B: You could create a Windows Authentication login for each domain user,
configure the logins as database users for the appropriate database, and add each database user to the db_datareader and db_datawriter database roles but it would require less administrative effort to create a Windows Authentication login for each domain group.
C: You can use the domain groups to configure access to the databases but you should not add each database user to the db_ddladmins database role as this violates the principle of least privilege.
D: You could create a Windows Authentication login for each domain user but it would require less administrative effort to create a Windows Authentication login for each domain group. Furthermore, you should not add each database user to the db_ddladmins database role as this violates the principle of least privilege.


QUESTION 103
You work as the database administrator at Certkiller .com. Certkiller .com has five departments named Accounting, Sales, Marketing, Research and Manufacturing. The Certkiller .com network consists of a single Active Directory domain named Certkiller .com. All servers on the Certkiller .com network run on Windows Server 2000 and all client computers run Windows XP Professional. Certkiller .com has a Certification Authority (CA) that has issued digital certificates to each client computer and each server on the Certkiller .com network. The Certkiller .com network contains a SQL Server 2000 database server named Certkiller -DB01. Certkiller -DB01 hosts a database named CK_Accounts that stores data for the Accounting department. Users in the Accounting department use an in-house client application to connect to the CK_Accounts database. Rory Allen is the manager of the Accounting department. Rory Allen is concerned about the security of the data transmitted between Certkiller -DB01 and the in-house client application. You are required to address this matter by implementing a security solution that will require authentication and encryption. What should you do?

A. Implement a self-signed digital certificate on Certkiller-DB01, set the
ForceEncryption option on Certkiller -DB01 to Yes and restart SQL Server service.
B. Implement a self-signed digital certificate on Certkiller-DB01, set the
ForceEncryption option on Certkiller -DB01 to Yes and restart Certkiller -DB01.
C. Implement a self-signed digital certificate on Certkiller-DB01, set the
ForceEncryption option on Certkiller -DB01 to No and restart the SQL Server service.
D. Configure the Database Engine to use a digital certificate, set the ForceEncryption option on Certkiller -DB01 to No and restart Certkiller -DB01.
E. Configure the Database Engine to use a digital certificate, set the ForceEncryption option on Certkiller -DB01 to Yes and restart Certkiller -DB01.

Answer: E

Explanation: To configure SQL Server 2005 to encrypt data, you must configure the Database Engine to use a digital certificate in the SQL Server Configuration Manager, set the ForceEncryption option to Yes and restart the server.

Incorrect answers:

A: Certkiller .com has a Certification Authority (CA). You should use the CA to provide digital certificates for the Database Engine as the CA may reject the self-signed certificate. Also, once you make configuration changes to the SQL Server 2005 server, you must restart the server for the configuration changes to take effect. Restarting the SQL Server service will not ensure that the configuration changes will take effect.
B: Certkiller .com has a Certification Authority (CA). You should use the CA to provide digital certificates for the Database Engine as the CA may reject the self-signed certificate.
C: Certkiller .com has a Certification Authority (CA). You should use the CA to provide digital certificates for the Database Engine as the CA may reject the self-signed certificate. Also, if you want to ensure that data is encrypted you must set the ForceEncryption option to Yes. If the ForceEncryption option is set to No, then data encryption will only be used if the client requests it. Furthermore, once you make configuration changes to the SQL Server 2005 server, you must restart the server for the configuration changes to take effect. Restarting the SQL Server service will not ensure that the configuration changes will take effect.
D: If you want to ensure that data is encrypted you must set the ForceEncryption option to Yes. If the ForceEncryption option is set to No, then data encryption will only be used if the client requests it.



QUESTION 104

You work as the database administrator at Certkiller .com. The Certkiller .com network consists of a single Active Directory domain named Certkiller .com. All servers on the Certkiller .com network run on Windows Server 2000 while half the client computers run Windows 2000 Professional and the rest run UNIX. The
Certkiller .com network contains a SQL Server 2000 database server named Certkiller -DB01. Certkiller -DB01 contains a database named CK_Customers. All Certkiller .com users will require access to the CK_Customers database from the corporate network. You need to configure access to the CK_Customers database to meet these requirements using the least amount of administrative effort.
What should you do?

A. Configure the CK_Customers to support Mixed Mode Authentication. Create a SQL login for the Domain Users group. Create SQL logins with passwords for each UNIX user.
B. Configure the CK_Customers to support Windows Authentication. Create a SQL login for each domain user.
C. Configure the CK_Customers to support Mixed Mode Authentication. Create a SQL login for each domain user.
D. Configure the CK_Customers to support Windows Authentication. Create a SQL login for the Domain Users group.

Answer: A

Explanation: You need to use Mixed Mode Authentication to allow access to the database for the UNIX users. Each UNIX user will require a SQL login with a password. The Windows XP Professional users can be granted access collectively by granting the Domain Users group access to the database.

Incorrect answers:

B, D: You cannot use Windows Authentication as this will not allow the UNIX users to access the database.

C: You can create a SQL login for each user but it would require less administrative effort t to create a SQL Login for the Domain Users group and separate SQL Logins for the UNIX users.


QUESTION 105
You work as the database administrator at Certkiller .com. The Certkiller .com network consists of a single Active Directory domain named Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server and all client computers run Windows 2000 Professional. The Certkiller .com network contains a SQL Server 2000 database server named Certkiller -DB01. Certkiller -DB01 hosts a database named CK_DB.
Certkiller .com often employs several casual workers to ease the workload on the permanent staff. These casual workers are assigned valid Windows 2000 domain user accounts. You need to prevent these casual employees from connecting to an instance of SQL Server 2000.
What should you do?

A. A Windows 2000 group named Casual must be created. All Casual workers must be added to this group. The db_denydatareader and db_denydatawriter fixed database roles must then be added to this group.
B. A Windows 2000 group named Casual must be created. All casual workers must be added to this group. The Casual group must be denied login in SQL Server.
C. A user-defined database role named Casual must be created. All casual workers must be added to this role. This role must be denied access to CK_DB.
D. All casual workers must be assigned to the public database role. The public database role must be denied access to CK_DB.

Answer: B

Explanation: Only Windows user accounts can be denied login in SQL Server. This means that it is possible to ensure that all casual workers are incapable of connecting to SQL Server by placing them in a Windows 2000 group and deny login for this group in SQL Server.

Incorrect answers:
A: The db_denydatareader and db_denydatawriter fixed database roles must be specified within the context of a database. Thus the permissions associated with database roles are not applied until the user successfully connects to an instance of SQL Server. This means that the use has to be able to make a connection. This is in direct violation of what is stated in the question.
C: User-defined database roles must be specified within the context of a database. This means that Casual workers must first be allowed to connect to SQL Server before these roles will apply its permissions or deny its permissions. This is not what is required as the questions states that causal workers should not be allowed to connect to an instance of SQL Server at all.
D: You cannot add users to the public database role; all users are automatically members of public.


QUESTION 106
You work as the database administrator at Certkiller .com. The Certkiller .com network consists of a single Active Directory domain named Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server and all client computers run Windows 2000 Professional. The Certkiller .com network contains a SQL Server 2000 database server named Certkiller -DB01. Certkiller -DB01 hosts two databases named CK_DB01 and CK_DB02 respectively. CK_DB01 is used by the Sales department and CK_DB02 is used by the Finance department. A Certkiller .com worker named Rory Allen has access to both CK_DB01 and CK_DB02. Rory Allen has been transferred to the Research and Development department with the result that he no longer required access to CK_DB01. You need to ensure that he cannot access CK_DB01 for security reasons. To this end you check Rory Allen's Windows user account and found that it has not been added to any groups. And furthermore you also find that the Domain Users group has not been granted login in SQL server. You thus need to execute the appropriate statement to ensure that Rory Allen cannot access CK_DB01. It is up to you to decide which statement to execute.
What should you do?

A. USE CK_DB01
EXEC sp_denylogin 'RoryAllen'
B. USE CK_DB01
EXEC sp_dbremove 'RoryAllen'
C. USE CK_DB01
EXEC sp_revokelogin 'RoryAllen'
D. USE CK_DB01
EXEC sp_revokedbaccess 'RoryAllen'

Answer: D

Explanation: When one calls the sp_revokedbaccess stored procedure in the context of CK_DB01, then one effective remove the specified database user account and any permissions that are associated with that user account from the CK_DB01 database. You could also call the sp_dropuser stored procedure (not mentioned as an option), but this stored procedure exists primarily for backward compatibility.

Incorrect answers:
A: sp_denylogin stored procedure will prevent Rory Allen from connecting to SQL Server by using his Windows 2000 or Windows NT user account. If Rory Allen connects to SQL Server using Windows authentication, then he would not be able to connect to CK_DB02 for which he still required access. Also when calling the sp_denylogin stored procedure you should ensure that the appropriate domain name and backslash precede the
user account.
B: The sp_dbremove stored procedure will remove a database and all files associated with that database. Besides this would be the wrong context in which to call this stored procedure.
C: sp_revokelogin stored procedure will prevent Rory Allen from connecting to SQL Server by using his Windows 2000 or Windows NT user account. If Rory Allen connects to SQL Server using Windows authentication, then he would not be able to connect to CK_DB02 for which he still required access. Also when calling the sp_revokelogin stored procedure you should ensure that the appropriate domain name and backslash precede the user account.


QUESTION 107
You work as the database administrator at Certkiller .com. The Certkiller .com network consists of a single Active Directory domain named Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server and all client computers run Windows 2000 Professional. The Certkiller .com network contains a SQL Server 2000 database server named Certkiller -DB01. Certkiller -DB01 hosts two databases named CK_DB01 and CK_DB02 respectively. CK_DB01 is used by the Sales department and CK_DB02 is used by the Finance department. A Certkiller .com worker named Rory Allen has access to both CK_DB01 and CK_DB02. Rory Allen has been transferred to the Research and Development department with the result that he no longer required access to CK_DB01. You need to ensure that he cannot access CK_DB01 for security reasons. To this end you check Rory Allen's Windows user account and found that it has not been added to any groups. And furthermore you also find that the Domain Users group has not been granted login in SQL server. You thus need to execute the appropriate statement to ensure that Rory Allen cannot access CK_DB01. It is up to you to decide which statement to execute.
What should you do? (Each correct answer presents a complete solution. Choose TWO.)
A. USE CK_DB01
EXEC sp_dbremove 'RoryAllen'
B. USE CK_DB01
EXEC sp_revokelogin 'RoryAllen'
C. USE CK_DB01
EXEC sp_revokedbaccess 'RoryAllen'
D. Use Enterprise Manager to deny Rory Allen access to CK_DB01

Answer: C, D

Explanation: When one calls the sp_revokedbaccess stored procedure in the context of CK_DB01, then one effective remove the specified database user account and any permissions that are associated with that user account from the CK_DB01 database. You could also call the sp_dropuser stored procedure (not mentioned as an option), but this stored procedure exists primarily for backward compatibility. The Enterprise Manager can be used to prevent Rory Allen from accessing CK_DB01 without denying him access to CK_DB02.

Incorrect answers:

A: The sp_dbremove stored procedure will remove a database and all files associated with that database. Besides this would be the wrong context in which to call this stored procedure.
B: sp_revokelogin stored procedure will prevent Rory Allen from connecting to SQL Server by using his Windows 2000 or Windows NT user account. If Rory Allen connects to SQL Server using Windows authentication, then he would not be able to connect to CK_DB02 for which he still required access. Also when calling the sp_revokelogin stored procedure you should ensure that the appropriate domain name and backslash precede the user account.


QUESTION 108
You work as the database administrator at Certkiller .com. The Certkiller .com network consists of a single Active Directory domain named Certkiller .com. The Certkiller .com network contains a SQL Server 2000 database server named Certkiller -DB01. Certkiller .com stores its business information in several instances, including named instances of SQL Server 2000 on Certkiller -DB01.The majority of the Certkiller .com client computers run Windows-based operating systems like Microsoft Windows 2000, Microsoft Windows NT, Microsoft Windows ME and Microsoft Windows 9x. There are however, also several users with client computers that run UNIX-based computers. These users make use of custom client
applications that use TCP/IP. All these users require access to Certkiller -DB01. You received instruction to integrate the UNIX network clients seamlessly into the Windows-based environment. Each UNIX user will require different permissions in the database. In your solution you want to minimize the number of SQL Server components or features that must be implemented to support the UNIX-based network clients.
What should you do?

A. Grant login to each UNIX user's Windows user account and enable TCP/IP Sockets server Net-Library.
B. Create a standard SQL Server login for each UNIX user and enable Banyan VINES server Net-Library.
C. Create a standard SQL Server login for each UNIX user and enable TCP/IP Sockets server Net-Library.
D. Grant login to each UNIX user's Windows user account and enable Multiprotocol server Net-Library.
E. Create a standard SQL Server login for each UNIX user and enable Shared Memory server Net-Library.

Answer: C

Explanation: UNIX does not support Windows Authentication, this means you should configure SQL Server to support mixed mode authentication. Also because every UNIX user is to be assigned appropriate permissions within the database, they will all require a separate login. By default Named Pipes and TCP/IP Sockets server Net-Library are enabled. Of the
available options only TCP/IP Sockets server Net-Library is already enables on the server computers.

Incorrect answers:
A: This option is only partly correct insofar as that the TCP/IP Sockets server Net-Libraries must be enabled. However, UNIX does not support Windows Authentication.
B: It is not necessary to enable Banyan VINES server Net-Library because none of the network clients are using Banyan VINES client software.
D: Even though Multiprotocol Net-Libraries support TCP/IP, if you enable Multiprotocol server Net-Library, then you would fail to meet the stated requirement of minimizing the amount of SQL server features that are necessary to support UNIX clients. Also UNIX does not support Windows Authentication.
E: You do not need to install Shared Memory server Net-Library as it is not mentioned anywhere in the question that users will be working locally on SQL Server computers.

QUESTION 109
You work as the database administrator at Certkiller .com. The Certkiller .com network consists of a single Active Directory domain named Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server and all client computers run Windows 2000 Professional. The Certkiller .com network contains a SQL Server 2000 database server named Certkiller -DB01. Since you have just been appointed to the job, you need to familiarize yourself with the database environment that you will be working in. You then notice that several Certkiller .com users have valid SQL Server logins that are not mapped to user accounts. These users are thus able to access the Finance database where sensitive information is stored. This information is not for all to view. You thus need to ensure that these users cannot access the Finance database. You thus need to decide which statement would be appropriate to execute.
What should you do?

A. sp_revokelogin guest
B. sp_droplogin guest
C. USE [finance]
EXEC sp_revokedbaccess guest
D. USE master
EXEC sp_revokedbaccess guest

Answer: C

Explanation: The guest database user account is the only way a user with a valid login can access a specific database without being explicitly assigned a database user account or without being a member of a group. This is the 'unmapped' account. In this case the guest account has been created in the Finance database. You thus need to execute the p_revokedbaccess stored procedure in the context of the appropriate database and specifying the guest account.

Incorrect answers:

A: The sp_revokelogin guest stored procedure must be called specifying a Windows user account, and the guest account is a database user account. This is indicated by the lack of domain name or backslash.
B: The sp_droplogin guest stored procedure executed with the guest parameter will drop a standard SQL Server login names guest if such a login exists. This is not related to the database user account named guest.
D: The guest account cannot be dropped from the master database.

QUESTION 110
You work as the database administrator at Certkiller .com. The Certkiller .com network consists of a single Active Directory domain named Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server and all client computers run Windows 2000 Professional. The Certkiller .com network contains a SQL Server 2000 database server named Certkiller -DB01.
A Certkiller .com user named Rory Allen is a member of the Sales department. Rory Allen wants to create a database. To this end he issued the following statement from Query Analyzer:

CREATE DATABASE Customers
ON PRIMARY (NAME = Data1, FILENAME = 'D:\Customers1.mdf,
SIZE = 1000, MAXSIZE = 3000, FILEGROWTH = 500),
FILEGROUP Customers_FG2(NAME = DATA2, FILENAME = 'E:\Customers2.ndf,
SIZE = 1000, MAXSIZE = 3000, FILEGROWTH = 500)
LOG ON (NAME = Log, FILENAME = 'F:\Customerlog.ldf,
SIZE = 500, MAXSIZE = 1000, FILEGROWTH = 200)

This statement failed. You thus received instruction to enable Rory Allen to create this database. You thus need to decide which would be appropriate action to take. What should you do? (Each correct answer presents part of the solution. Choose THREE.)

A. Ensure that Rory Allen has a valid SQL Server login.
B. Ensure that Rory Allen has a valid Windows account.
C. Ensure that Rory Allen has a valid user account in the master database.
D. Execute the sp_addsrvrolemember 'RoryAllen' 'sysadmin' statement.
E. Execute the sp_changedbowner 'RoryAllen' statement.
F. Execute the GRANT ALL TO RoryAllen statement.
G. Execute the GRANT CREATE DATABASE TO RoryAllen statement.

Answer: A, C, G

Explanation: Rory is not able to create a database because his user account has not been assigned the CREATE DATABASE permission. To be able to assign this ermission to Rory Allen you also need to ensure that he has a valid SQL Server login as well as a user account in the master database. The CREATE DATABASE permission can only be granted in master.

Incorrect answers:

B : Ensuring a valid Windows account will not enable you to assign the CREATE DATABASE permission to Rory Allen.
D: If you execute the sp_addsrvrolemember 'RoryAllen' 'sysadmin' statement then you would in essence be adding Rory Allen's user account to the sysadmin fixed server role. Only if Rory Allen was a member of sysadmin, would he be able to perform any action in that instance of SQL Server, including database creation. However, only administrators are usually members of this role.
E: If you execute the sp_changedbowner 'RoryAllen' statement then you would change
the owner of the database stored procedure. This action is not applicable in this case.
F: The GRANT ALL TO Rory Allen will grant permission to successfully execute all statements except for CREATE DATABASE to Rory Allen's user account.