Microsoft Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 : 070-457 Exam Questions

You administer a Microsoft SQL Server 2012 database named ContosoDb. The database contains a table named Suppliers and a column named IsActive in the Purchases schema. You create a new user named ContosoUser in ContosoDb. ContosoUser has no permissions to the Suppliers table. You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions. Which Transact-SQL statement should you use?

• A. GRANT SELECT ON Purchases.Suppliers TO ContosoUser
• B. CREATE PROCEDURE Purchases.PurgeInactiveSuppliers WITH EXECUTE AS USER = 'dbo' AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser
• C. GRANT DELETE ON Purchases.Suppliers TO ContosoUser
• D. CREATE PROCEDURE Purchases.PurgeInactiveSuppliers AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0
  GO
  GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser

You administer a Microsoft SQL Server 2012 instance. The instance contains a database that supports a retail sales application. The application generates hundreds of transactions per second and is online 24 hours per day and 7 days per week. You plan to define a backup strategy for the database. You need to ensure that the following requirements are met:
No more than 5 minutes worth of transactions are lost.
Data can be recovered by using the minimum amount of administrative effort.
What should you do? Choose all that apply.

• A. Create a LOG backup every 5 minutes.
• B. Create a DIFFERENTIAL database backup every 4 hours.
• C. Configure the database to use the FULL recovery model.
• D. Create a DIFFERENTIAL database backup every 24 hours.
• E. Create a FULL database backup every 24 hours.
• F. Configure the database to use the SIMPLE recovery model.

You develop a Microsoft SQL Server 2012 database that has two tables named SavingAccounts and LoanAccounts. Both tables have a column named AccountNumber of the nvarchar data type. You use a third table named Transactions that has columns named TransactionId, AccountNumber, Amount, and TransactionDate. You need to ensure that when multiple records are inserted in the Transactions table, only the records that have a valid AccountNumber in the SavingAccounts or LoanAccounts are inserted. Which Transact-SQL statement should you use?

• A. CREATE TRIGGER TrgValidateAccountNumber ON Transactions FOR INSERT AS BEGIN
  IF EXISTS (
  SELECT AccountNumber FROM inserted EXCEPT
  (SELECT AccountNumber FROM LoanAccounts
  UNION SELECT AccountNumber FROM SavingAccounts))
  BEGIN
  ROLLBACK TRAN
  END
  END
• B. CREATE TRIGGER TrgValidateAccountNumber ON Transactions INSTEAD OF INSERT AS BEGIN
  INSERT INTO Transactions SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) END
• C. CREATE TRIGGER TrgValidateAccountNumber ON Transactions INSTEAD OF INSERT AS BEGIN
  IF EXISTS (
  SELECT AccountNumber FROM inserted EXCEPT
  (SELECT AccountNumber FROM LoanAccounts
  UNION SELECT AccountNumber FROM SavingAccounts))
  BEGIN
  ROLLBACK TRAN
  END
  END
• D. CREATE TRIGGER TrgValidateAccountNumber ON Transactions FOR INSERT AS BEGIN
  INSERT INTO Transactions SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) END

You have three tables that contain data for vendors, customers, and agents. You create a view that is used to look up telephone numbers for these companies. The view has the following definition: You need to ensure that users can update only the phone numbers by using this view. What should you do?

• A. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.
• B. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.
• C. Create an INSTEAD OF UPDATE trigger on the view.
• D. Create an AFTER UPDATE trigger on the view.

You are migrating a database named Orders to a new server that runs Microsoft SQL Server 2012. You
attempt to add the [Corpnet\User1] login to the database. However, you receive the following error
message:
"User already exists in current database."
You need to configure the [Corpnet\User1] login to be able to access the Orders database and retain the
original permissions. You need to achieve this goal by using the minimum required permissions.
Which Transact-SQL statement should you use?

• A. DROP USER [User1]; CREATE USER [Corpnet\User1] FOR LOGIN [Corpnet\User1]; ALTER ROLE [db_owner] ADD MEMBER [Corpnet\User1];
• B. ALTER ROLE [db_owner] ADD MEMBER [Corpnet\User1];
• C. ALTER SERVER ROLE [sysadmin] ADD MEMBER [Corpnet\User1];
• D. ALTER USER [Corpnet\User1] WITH LOGIN [Corpnet\User1];