You administer a Microsoft Azure SQL Database database named Inventory that contains a stored procedure named p_AddInventory.
Users need to be able to SELECTfrom all tables in the database and execute the stored procedure.
You need to grant only the necessary permissions.
What should you do?

• A.Grant EXECUTE permission on p_AddInventory to all users. Add all users to the db_datawriter role.
• B.Add EXECUTE AS OWNER to the stored procedure. Add all users to the db_datareader role
• C.Create a new database role. Grant EXECUTE permission on p_AddInventory to the new role. Grant VIEW DEFINITION to the role. Add all users to the role
• D.Create a new database role. Grant EXECUTE permission on p_AddInventory to the new role. Add all users to the role. Add all users to the db_datareader role

Comment: *

Name: *

Email: *

Verification: *

DRAG DROP
You administer a Microsoft SQL Server database.
You use an OrderDetail table that has the following definition:

You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to include only rows that contain a value in the SpecialOfferID column.
Which four Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.) Select and Place:

Correct Answer:

Explanation/Reference:
Explanation:
According to these references, this answer looks correct.
References:
http://msdn.microsoft.com/en-us/library/ms188783.aspx
http://msdn.microsoft.com/en-us/library/ms189280.aspx

Comment: *

Name: *

Email: *

Verification: *

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 DELETE ON Purchases. Suppliers TC ContosoUser
• B.CREATE PROCEDURE Purchases.PurgeInactiveSuppliersWITH EXECUTE AS USER = 'dbo'ASDELETE FROM Purchases.Suppliers WHERE IsActive = 0GOGRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser
• C.GRANT SELECT ON Purchases.Suppliers TO ContosoUser
• D.CREATE PROCEDURE Purchases. PurgeInactiveSuppliersASDELETE FROM Purchases.Suppliers WHERE IsActive = 0GOGRANT EXECUTE ON Purchases. PurgeInactiveSuppliers TO ContosoUser

Comment: *

Name: *

Email: *

Verification: *

You administer a Microsoft SQL Server 2012 instance.
You discover that the SQL Agent Error Log file is rapidly growing in size.
You need to ensure that the SQL Agent Error Log file does not grow rapidly when SQL Server agent jobs execute.
What should you do?

• A.Execute the sp_cycle_agent_errorlog stored procedure.
• B.Disable the Include execution trace messages feature.
• C.Configure event forwarding.
• D.Enable the Auto Shrink option on the master database.
• E.Enable the Auto Shrink option on the msdb database.

Comment: *

Name: *

Email: *

Verification: *

You administer a Microsoft SQL Server instance named SQL2012 that hosts an OLTP database of 1 terabyte in size.
The database is modified by users only from Monday through Friday from 09:00 hours to 17:00 hours.
Users modify more than 30 percent of the data in the database during the week.
Backups are performed as shown in the following schedule:

The Finance department plans to execute a batch process every Saturday at 09:00 hours. This batch process will take a maximum of 8 hours to complete.
The batch process will update three tables that are 10 GB in size. The batch process will update these tables multiple times.
When the batch process completes, the Finance department runs a report to find out whether the batch process has completed correctly.
You need to ensure that if the Finance department disapproves the batch process, the batch operation can be rolled back in the minimum amount of time.
What should you do on Saturday?

• A.Perform a differential backup at 08:59 hours.
• B.Record the LSN of the transaction log at 08:59 hours. Perform a transaction log backup at 08:59 hours.
• C.Create a marked transaction in the transaction log at 08:59 hours. Perform a transaction log backup at
  17:01 hours.
• D.Record the LSN of the transaction log at 08:59 hours. Perform a transaction log backup at 17:01 hours.
• E.Create a marked transaction in the transaction log at 08:59 hours. Perform a transaction log backup at
  08:59 hours.
• F.Create a database snapshot at 08:59 hours.

Comment: *

Name: *

Email: *

Verification: *