MSCE Interview Questions and Answers

QUESTION 111
You work as the database administrator at Certkiller .com. The Certkiller .comnetwork 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_Sales.
A Certkiller .com user named Amy Wilson connects to SQL Server via a SQL server login named Amy. Amy Wilson's database user account has been assigned permission to create tables in CK_Sales. Amy has been offered o job by a rival company and took the opportunity to leave Certkiller .com. To this end you need to remove Amy Wilson's credential from SQL Server. You execute the following statement: sp_droploginAmy
Unfortunately executing this statement does not have the desired effect and the login was not removed. You need to execute the appropriate statement to remove Amy Wilson's credentials.
What should you do?

A. Execute the sp_revokedblogin Amy statement.
B. Execute the sp_denylogin Amy statement.
C. Execute the USE CK_Sales
EXEC sp_revokedbaccess Amy
EXEC sp_droplogin Amy statement.
D. Execute the USE CK_Sales
EXEC sp_denydbaccess Amy
EXEC sp_droplogin Amy statement.

Answer: C

Explanation: You cannot remove Amy Wilson's SQL Server login by executing the sp_droplogin statement since Amy Wilson's database user account has been granted permission to CREATE TABLE in CK_Sales and her login would be mapped to the database user account. You first need to remove her database account from CK_Sales. Once successfully removed from CK_Sales, you can remove Amy Wilson's SQL Server login. If you make use of Enterprise Manager to remove Amy Wilson's login then all user accounts to which this login is mapped will automatically be dropped.

Incorrect answers:

A: If you execute the sp_revokedblogin Amy statement you in essence would be revoking login from a Windows 2000 or Windows NT user or group account. If Amy Wilson's user account were granted login, then performing this action without removing the database account will result in the Amy Wilson database user account becoming orphaned. This is not exactly removing Amy Wilson's credentials from SQL Server.
B: If you execute the sp_denylogin Amy statement then you would be denying login to Windows-based user accounts or groups.
D: There is no such statement as sp_denydbaccess in SQL Server.


QUESTION 112
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. The following table shows the database roles that you have created and the assigned permissions to a Customers table:

A Certkiller .com user named Clive Wilson has Distribution database role
membership. While carrying out your duties as database administrator you
conducted a routine audit of database activity. In this audit you found that Clive Wilson has been inserting data into the Distribution table. This is not in the interest of Certkiller .com and you need to revoke the INSERT permission from the Clive Wilson user account. To this end you revoke the INSERT permission from the Clive Wilson user account. However, later, you discover that Clive Wilson is still able to ass rows to that table. You need to ensure that Clive Wilson will not be able to successfully issue INSERT statements against the Customers table.

You need to take the appropriate action to accomplish this task whilst ensuring that Clive Wilson is not granted any additional permissions in the dataset and that the other user's permissions are not affected.
What should you do?

A. Clive Wilson user account must be denied the INSERT permission for Customers.
B. Distribution should be denied the INSERT permission for Customers.
C. The Clive Wilson user account should be added to the Finance database.
D. The Clive Wilson user account should be added to the Sales database.

Answer: A

Explanation

in the event of a user being assigned multiple rights for the same object, all the permissions that are assigned to a user's database account and all permissions that are assigned to database roles or group accounts of which the user enjoys membership is combined. This means the granted permissions are cumulative, and denied permissions override granted permissions of the same type for the same object, and revoked permissions clear the previously assigned permissions. Because it is mentioned in the question that you revoked Clive Wilson's INSERT permission from his user account, his user account does not explicitly have this permission for the ustomers table. But it is also mentioned that Clive Wilson is a member of Distribution which does have the INSERT permission granted. Thus you should deny the INSERT permission to Clive Wilson's user account. This will ensure that he will not be able to add rows to the table and the other users in the Distribution group will not be affected even if he remain a member of the Distribution group deny permissions overrides granted ones of the same type.

Incorrect answers:

B: If you deny the Distribution group the INSERT permission than other users in the group will also be affected.
C: If you add Clive Wilson's user account to the Finance database then you would be preventing him from successfully inserting rows , but he would then also be able to successfully execute the UPDATE statement. It is mentioned pertinently in the question that you do not want Clive Wilson to be granted additional permissions in Customers.
D: If you add Clive Wilson's user account to the Sales database then you will not be preventing him from adding rows to Customers. Instead Clive Wilson will then have the ability to modify data in Customers.

QUESTION 113
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_Sales.
There are certain Certkiller .com users that make use of an application. This application is multifunctional in that it allows these users to connect to CK_Sales via an application. It also allows them to update information in CK_Sales. The application also authenticates the user who starts the program and, depending upon the current user's identity, allows the user to manipulate data in CK_Sales. A Certkiller .com user named Amy Wilson is one of these members that make use of the application to connect to CK_Sales. Amy Wilson is not allowed to change users' personal information in the Human Resource table in CK_Sales. Under normalcircumstances she cannot change these details, however, then she connects to SQL

Server via the application, she finds herself able to change data in the Human Resource table successfully. Since she is not allowed to make these changes, you received instruction to ensure that Amy Wilson cannot make changes of this type whilst ensuring that her existing permissions and privileges that were directly assigned to her user account and through her memberships are not affected.
What should you do? (Each correct answer presents a complete solution. Choose TWO.)

A. The UPDATE permission for the Human Resource table must be revoked for Amy Wilson.
B. The UPDATE permission for the Human Resource table must be denied for Amy Wilson.
C. The application code should be modified to NOT use an application role when Amy Wilson is running the application.
D. The application code should be modified to make use of Windows Authentication and not SQL Server authentication.
E. An additional application role must be created for Amy Wilson.
The application should be coded to activate that role when Amy Wilson runs the application.

Answer: C, E

Explanation: When taking into consideration the multifunctional nature of the application then one can deduce that once the application has connected to SQL server, it can either continue to run under the current user's security context or it can invoke the application role by calling the sp_setapprole stored procedure within the context of the appropriate database. In the event of the application relying on the current user's security context, then the CK_Sales permissions that have been granted to the user are applied. And if the application is configured to activate an application role, then the permissions assigned to the application role are
applied. The current user permissions are superseded by the application role permissions.

In this scenario Amy Wilson belongs to a user group that has been assigned permission to update information in CK_Sales and the application has probably been coded to use a specific application role when a member of that group runs the application. Also Amy Wilson is a member of this group, she must not be allowed this permission and this means that you cannot remove her from the group as it is also stipulated in the question
that her current permissions should not be affected. Thus you should configure the application to respond differently when Amy Wilson runs the application:
You can code the application to run under the Amy Wilson security context rather than activating the application role when Amy Wilson starts the application. OR You could create an additional application role for Amy Wilson and code the application to activate that role when Amy Wilson runs the application.

Incorrect answers:

A: The question states that Amy Wilson under normal circumstances she cannot change these details, however, then she connects to SQL Server via the application, she finds herself able to change data in the Human Resource table successfully. Thus, the UPDATE permission has not been granted to her user account and thus you are not required to deny or revoke it.
B: The question states that Amy Wilson under normal circumstances she cannot change these details, however, then she connects to SQL Server via the application, she finds herself able to change data in the Human Resource table successfully. Thus, the UPDATE permission has not been granted to her user account and thus you are not required to deny or revoke it.
D: Even if the application makes use of Windows authentication instead of SQL Server authentication is irrelevant to this case.

QUESTION 114
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_Customers.
CK_Customers includes 35 tables. These tables all contain data that should be Read-Only. You are required to prevent all users from modifying these tables whilst using the least amount of administrative effort.
What should you do?

A. Create a view for each table and deny the INSERT, UPDATE and DELETE
permissions for each view.
B. The INSERT, UPDATE and DELETE permissions to the public role should be denied for each table.
C. The SELECT, INSERT, UPDATE and DELETE permissions to the public role should be denied for each table.
D. An application role must be created and all the database user accounts should be added to this role.
Then the INSERT, UPDATE and DELETE permissions should be denied for that role.
E. Create a Read-Only file group and move the 35 tables to that filegroup.

Answer: B

Explanation: Object permissions such as SELECT, INSERT, UPDATE, DELETE,
REFERENCES and EXECUTE can be granted or denied by administrators to exercise control over the extent to which users are able to manipulate data. To prevent users from modifying data that are contained in the lookup tables, amongst the presented choices, you should deny the INSERT, UPDATE and DELETE to the public role which contains all the database user accounts. This will ensure that the tables are rendered Read-Only for
all users except for members of the sysadmin fixed server role.

Incorrect answers:

A: This option is a viable solution, however, this option will also require that you need to deny the same permissions for each table as well in order to prevent users from accessing tables directly. This amounts to more administrative effort than is required.
C: Denying SELECT to the public role for each table will prevent users from retrieving data from these tables.
D: Application roles cannot contain database user accounts.
E: This option will also work, but it involves extra administrative effort and considerations such as sufficient space and additional strain on the system resources. The extra administrative effort can be negated by simply denying object permissions to the public role.


QUESTION 115
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_Sales.
You received a report from the Certkiller .com Sales manager named Rory Allen. In his report Rory Allen mentions that data in CK_Sales has been improperly manipulated. You need to investigate this issue and consequently discovered that only members of a user-defined database role named DBAdmins have the necessary permissions to manipulate data in this particular way. You analyze a SQL Server audit log and found that a Certkiller .com user named Andy Reid was the one responsible for this change. Andy Reid is not supposed to be a member of DBAdmins. You investigation of the audit log also revealed that Clive Wilson added Andy Reid to this role. You need to prevent this situation from happening again and thus remove Andy Reid's user account from DBAdmins.

There is however an additional step required to prevent Andy Reid from beingreassigned to the DBAdmins database role.
What should you do?

A. Andy Reid should be assigned to the db-denydatareader fixed database role.
B. Andy Reid should be assigned to the db_denydatawriter fixed database role.
C. Clive Wilson should be removed from the db_securityadmin fixed database role.
D. Clive Wilson should be removed from the db-accessadmin fixed database role.

Answer: C

Explanation: Due to Clive Wilson's membership of the db_securityadmin fixed
database role, he was able to add Andy Reid to the DBAdmins group. You need to remove Clive Wilson from this role to prevent him from adding users to roles and thereby granting them inappropriate permissions within CK_Sales.

Incorrect answers:

A: Assigning Andy Reid to the db_denydatareader fixed database role may be
inappropriate depending on his job requirements. This database role prevents users from selecting information from a database and it could be that Any Reid needs to be able to read data in CK_Sales.
B: Assigning Andy Reid to the db_denydatawriter fixed database role may be
inappropriate depending on his job requirements. The db_denydatawriter database role prevents users from changing information in a database.
D: Having membership of the db_accessadmin fixed database role allows Clive Wilson the ability to add and remove database users, but it does not enable him to grant or deny any permissions in the database or control database role memberships.


QUESTION 116
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_Merchandise.
Several users make use of a custom application to access CK_Merchandise. This custom application allows them to update information in the ProductsOnHand table. All these users will require the same level of permissions. The custom application has been coded to authenticate users internally and to access CK_Merchandise by using an application role named DataEntry. You are required to create a login for the application whilst using the least amount of administrative effort.
What should you do?

A. A Windows user account named Certkiller \DataEntry should be created.
Then grant login to the Certkiller \DataEntry account.
B. A Windows user account named DataEntry should be created.
Then assign the login permission for it.
C. A SQL Server account named DataEntry should be created.
Then code the custom application to use this account.
D. A SQL Server login must be created for each user.
Then code the custom application to use the login of the user who is running the custom application.

Answer: C

Explanation: Database application's function is to ease the usage of the database. Thus it can be coded to call stored procedures in the database, provide security, authenticate users internally, to connect using the appropriate instance of SQL Server. In this case the custom application can be coded to use a single SQL Server login and a single application role because it is mentioned that users should have the same level of
permissions in CK_Merchandise while connected to it via the custom application. It also is not stipulated that auditing is required thus it will not be necessary to have SQL Server determine the current user identity.

Incorrect answers:

A: Client applications such as this custom application run under the
security context of the user that started the application.
B: When considering granting a login to a Windows user account, the domain name and the backslash should precede the user name. However, client applications cannot run under another user's security context, they run under the security context of the user who started the application.
D: Creating a SQL Server login for each user and coding the custom application to use that login will require unnecessary administrative effort than is required under the circumstances. All that is required is to create a single SQL Server login for the custom application.


QUESTION 117
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_Information.
Often it happens that data errors occur in CK_Information because the
Certkiller .com employees do not make proper use of the SQL statements when
executing them. As a result Certkiller .com issued a new company directive: a custom application to prevent users from directly accessing the database must be implemented. Now all employees are required to access CK_Information through the custom application. You need to comply with the new directive and must ensure that no employee is able to directly access CK_Information. To this end you revoke all appropriate permissions from each employee's database user account. Now you need to decide on the following step.
What should you do?

A. Code the application to use the db_owner fixed database role.
B. Code the application to run under a current user's account security context.
C. Create a user-defined database role and code the application to use it.
D. Create an application role and code the application to use it.

Answer: D

Explanation: the application should be coded to use a SQL Server login to connect to CK_Information. Then the application should be coded to call the sp_setapprole stored procedure in the context of the appropriate database. The application will supply the appropriate password, and an application role will be activated. The application role must be granted the appropriate [permissions in the data base so as to allow users to perform job-related functions. When a user runs the application and the application role has been activates, any permissions associated with that user, even those through group memberships will be ignored. The permissions that have been assigned to the application role will become the resultant effective permissions for that user.

Incorrect answers:

A: Making use of fixed database roles is not a recommended practice in this type of scenario because some users might be able to use the login and the associated user account to directly access CK_Information.
B: If the application is coded to run under the security context of a current user, then the user's account would have to be assigned the necessary permissions to access CK_Information. This means that the user will be able to access CK_Information directly.
C: Making use of user-defined database roles is not a recommended practice in this type of scenario because some users might be able to use the login and the associated user account to directly access CK_Information.

QUESTION 118
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. Certkiller .com is currently in a jointventure with a partner company. The Certkiller .com network contains a SQL Server 2000 database server named Certkiller -DB01. Certkiller -DB01 hosts a database named CK_Finance.
Access to CK_Finance must be restricted to authenticated users only. As such no guest account has been created in CK_Finance. (A guest account enables users who have a valid login but no user account in CK_Finance to access the database.) The Guest Windows user account in the Certkiller .com domain has been granted login in SQL Server. The Guest Windows user account is used by the partner companies who require limited access to SQL Server. You discovered that the partner company that makes use of this account can gain access to CK_Finance.

This is unacceptable and you have thus received instruction to ensure that users that connect to SQL Server using the domain Guest Windows user account should not be able to access CK_Finance. You thus need to decide on the appropriate statement to execute.
What should you do?

A. Execute the USE CK_Finance
EXEC sp_revokedbaccess guest statement.
B. Execute the USE CK_Finance
EXEC sp_dropuser guest statement.
C. Execute the sp_revokelogin ' Certkiller \guest' statement.
D. Execute the USE CK_Finance
EXEC sp_revokedbaccess ' Certkiller \guest' statement.

Answer: D

Explanation: The guest database user account is not automatically created. An administrator has to create the guest account within the context of a database, but no login can be mapped to the guest account in any given database. This means that the created guest account in CK_Finance will permit a user with a valid login, but without a user account in CK_Finance to access the database with the permissions that have been assigned to guest and to the public database role, of which all database users accounts
enjoy membership. To prevent he partner company from accessing CK_Finance while still ensuring that they are able to log in to SQL Server, you should execute the sp_revokedbaccess stored procedure in the context of CK_Finance and specify the domain name and the Guest Windows user account.

Incorrect answers:

A: When one calls the sp_revokedbaccess stored procedure in the context of
CK_Finance, then one effectively remove the specified database user account and any permissions that are associated with that user account from the database. This is not what is required in this scenario.
B: When one calls the sp_dropuser stored procedure in the context of CK_Finance, then one effectively remove the specified database user account and any permissions that are associated with that user account from the database. This is not what is required in this scenario.
C: If you execute the sp_revokelogin ' Certkiller \guest' statement, then you would be preventing the partner company from logging in to SQL Server and this is not what is required since they should only be prevented from accessing CK_Finance.

QUESTION 119
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_Sales.
There are several Certkiller .com employees that require the same level of access to CK_Sales. These employees are all members of a Windows 2000 group named Research in the Certkiller .com domain. You are required to provide these employees with access to CK_Sales. You thus need to execute the appropriate statement.
What should you do?

A. Execute the sp_grantdbaccess 'Research'
USE CK_Sales
EXEC sp_grantlogin 'Research' statement
B. Execute the sp_grantlogin Certkiller \Research
USE CK_Sales
sp_grantdbaccess Certkiller \Research statement
C. Execute the sp_grantlogin ' Certkiller \Research'
USE CK_Sales
EXEC sp_grantdbaccess ' Certkiller \Research', Research statement
D. Execute the USE CK_Sales
sp_grantlogin 'Research'
EXEC sp_grantdbaccess 'Research' statement

Answer: C

Explanation:

To allow the Research group members access to SQL Server, you should
first grant login to their Windows 2000 group. Do this by executing sp_grantlogin stored procedure. Since it is mentioned in the question that Research exists within a Windows 2000 domain, the group name must be preceded by the domain name and the backslash. To enable the research group members to access CK_Sales, you need to map the Windows 2000 login to a user account in that database. Do this by executing the sp_grantdbaccess stored procedure in the context of the appropriate database. In this case CK_Sales. Do include the login name and the database user account as parameters. If the database user account is omitted then the procedure will create a database user account with the same name as the specified login.

Incorrect options:

A: Single quotation marks around the Windows 2000 group or user account is required as the appropriate syntax. In this case the Windows 2000 domain name should also be included.
B: EXEC is required when the statement call a stored procedure that is not the first in a batch.
D: Windows group or user accounts must be assigned login before they can be granted access to a particular database.

QUESTION 120
You work as the database administrator at Certkiller .com. Each department in the company is run as a separate domain on the Certkiller .com network. 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_Information.
A Certkiller .com user named Mia Hamm makes use of CK_Information to perform certain job-related functions. Mia Hamm enjoys membership of the Sales domain. Currently her Windows domain user account is granted login to the SQL Server. In the following week Mia Hamm will be filling in for an employee in the Marketing department. This arrangement will be valid for a whole month. Thus during this month Mia Hamm is not allowed to log in to SQL Server. However, upon her return to the Sales domain, her existing permissions in CK_Information should be reinstated. You have been assigned to address this issue with the least amount of effort.
What should you do? (Each correct answer presents a complete solution. Choose TWO.)

A. You should execute the sp_revokelogin 'MiaHamm' statement.
B. You should execute the sp_revokelogin 'Sales\MiaHamm' statement.
C. You should execute the sp_dropuser 'Sales\MiaHamm' statement.
D. You should execute the sp_denylogin 'MiaHamm' statement.
E. You should execute the sp_denylogin 'Sales\MiaHamm' statement.
F. Open Enterprise Manager and right click Mia Hamm's login. Select Deny Access on the General tab of the Properties sheet.

Answer: E, F

Explanation: From the question one can deduce that Mia Hamm uses her Windows domain user account to connect to SQL Server. Thus to prevent her from accessing CK_Information while preserving her permissions, you should execute the sp_denylogin stored procedure and specify the appropriate domain name and user account. Reinstatement can be achieved by merely executing the sp_grantlogin stored procedure specifying the appropriate domain name and user account. You can also open Enterprise Manager and right-click Mia Hamm's login to Deny Access on the General tab. Reinstatement is done my mere selecting the Grant Access option on
the same tab.

Incorrect answers:

A: Executing the sp_revokelogin 'MiaHamm' statement will remove logins associated with Windows 2000 or Windows NT groups. This stored procedure cannot be executed when you do not specify the user's domain and account information.
B: Executing the sp_revokelogin 'Sales\MiaHamm' statement will result in her login and all subsequent permissions and role memberships being removed from the database. Thus you will need to recreate her login and all subsequent permissions and role memberships upon her return. Not quite the least amount of effort.
C: The sp_dropuser 'Sales\MiaHamm' statement is used for backward compatibility purposes and must be executed wit in the context of a database.
D: Executing the sp_denylogin 'MiaHamm' statement would be incorrect in that you will also require to specify the domain name and the user account.

Microsoft SQL Server 2000 Books Online (2004), Contents: Administering SQL Server,
"Managing Security", "Managing Security accounts," Denying Login access to Windows
Accounts."