Download Microsoft.70-462.Prep4Sure.2018-12-01.180q.tqb
| Vendor: | Microsoft |
| Exam Code: | 70-462 |
| Exam Name: | Administering Microsoft SQL Server 2012/2014 Databases |
| Date: | Dec 01, 2018 |
| File Size: | 5 MB |
Demo Questions
Question 1
You administer a Microsoft SQL Server database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views.
You grant User1 access to execute the stored procedure.
You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required.
Which two actions should you perform? Each correct answer presents part of the solution.
- Grant the VIEW SERVER STATE permission to User1.
- Move the stored procedure to the User1 schema.
- Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure.
- Grant the db_datareader role on the database to User1.
- Create a SQL Server login that has VIEW SERVER STATE permissions. Modify the stored procedure to include the EXECUTE AS (newlogin) statement.
Correct answer: CE
Explanation:
References:http://msdn.microsoft.com/en-us/library/ms187861.aspxhttp://msdn.microsoft.com/en-us/library/ms191291.aspx References:
http://msdn.microsoft.com/en-us/library/ms187861.aspx
http://msdn.microsoft.com/en-us/library/ms191291.aspx
Question 2
You are migrating a database named Orders to a new server that runs Microsoft SQL Server.
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?
- DROP USER [User1];CREATE USER [Corpnet\User1] FOR LOGIN [Corpnet\User1];ALTER ROLE [db_owner] ADD MEM3ER [Corpnet\User1];
- ALTER SERVER RCLS Isysadmin] ADD MEMBER [Corpnet\User1];
- ALTER USER [Corpnet\User1] WITH LOGIN [Corpnet\User1];
- ALTER ROLE [db owner] ADD MEMBBR [Corpnet\User1];
Correct answer: C
Explanation:
Reference:http://msdn.microsoft.com/en-us/library/ms176060.aspx Reference:
http://msdn.microsoft.com/en-us/library/ms176060.aspx
Question 3
You administer a Microsoft SQL Server 2012 database.
You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements:
You attempt to restore the Orders database and the restore fails. You copy the encryption file to the original location.
A hardware failure occurs and so a new server must be installed and configured.
After installing SQL Server to the new server, you restore the Orders database and copy the encryption files to their original location. However, you are unable to access the database.
You need to be able to restore the database.
Which Transact-SQL statement should you use before attempting the restore?
Correct answer: C
Explanation:
To create a database protected by transparent data encryption The following procedures show you have to create a database protected by TDE using SQL Server Management Studio and by using Transact-SQL. Using SQL Server Management Studio 1. Create a database master key and certificate in the master database. 2. Create a backup of the server certificate in the master database. Etc. In transact sql:-- Create a database master key and a certificate in the master database. USE master ; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1'; GO CREATE CERTIFICATE TestSQLServerCert WITH SUBJECT = 'Certificate to protect TDE key' GO -- Create a backup of the server certificate in the master database. -- The following code stores the backup of the certificate and the private key file in the default data location for this instance of SQL Server -- (C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA). BACKUP CERTIFICATE TestSQLServerCert TO FILE = 'TestSQLServerCert' WITH PRIVATE KEY ( FILE = 'SQLPrivateKeyFile', ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1' ); GO Etc. ' References:https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/move-a-tde-protected-database-to-another-sql-server To create a database protected by transparent data encryption
The following procedures show you have to create a database protected by TDE using SQL Server Management Studio and by using Transact-SQL.
Using SQL Server Management Studio
1. Create a database master key and certificate in the master database.
2. Create a backup of the server certificate in the master database.
Etc.
In transact sql:
-- Create a database master key and a certificate in the master database.
USE master ;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';
GO
CREATE CERTIFICATE TestSQLServerCert
WITH SUBJECT = 'Certificate to protect TDE key'
GO
-- Create a backup of the server certificate in the master database.
-- The following code stores the backup of the certificate and the private key file in the default data location for this instance of SQL Server
-- (C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA).
BACKUP CERTIFICATE TestSQLServerCert
TO FILE = 'TestSQLServerCert'
WITH PRIVATE KEY
(
FILE = 'SQLPrivateKeyFile',
ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1'
);
GO
Etc.
'
References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/move-a-tde-protected-database-to-another-sql-server
