70-764 Administering a SQL Database Infrastructure

msdb has additional fixed roles for ________

Options are :

  • SSIS
  • Data Collection
  • Mirroring
  • All options are true (Correct)

Answer : All options are true

Explanation msdb has additional fixed roles for SSIS, Data Collection, Mirroring, Policy based management and server groups.

70-764 Administering a SQL Database Infrastructure

Which fixed roles are common to all databases?

Options are :

  • db_owner (Correct)
  • db_datareader (Correct)
  • db_ddladmin (Correct)
  • public (Correct)

Answer : db_owner db_datareader db_ddladmin public

Explanation db_owner, db_securityadmin, db_accessadmin, db_backupoperator, db_ddladmin, db_datawriter, db_datareader, db_denydatawriter, db_denydatareader, public etc. fixed roles are common to all databases.

Which permission grants access to bulk insert operations?

Options are :

  • ADMINISTER BULK OPERATIONS (Correct)
  • CONTROL SERVER
  • ALTER ANY DATABASE
  • CREATE ANY DATABASE

Answer : ADMINISTER BULK OPERATIONS

Explanation ADMINISTER BULK OPERATIONS: It grants access to bulk insert operations. CONTROL SERVER: It implicitly grants all other server level permissions. CREATE ANY DATABASE: It grants the right to create new databases. ALTER ANY DATABASE: It implicitly grants the CREATE ANY DATABASE permission.

True or False: ALTER SETTINGS permission grants EXECUTE permission on sp_configure.

Options are :

  • True (Correct)
  • False

Answer : True

Explanation The given statement is true. ALTER SETTINGS permission grants EXECUTE permissions on sp_configure and the RECONFIGURE command.

70-764 Administering a SQL Database Infrastructure Tests

You have to configure a SQL Server instance to ensure that a user named U1 can send mail by using DatabseMail.

Solution: You add the DatabaseMailUserRole to U1 in the msdb database. Does this solution meet the goal?

Options are :

  • Yes (Correct)
  • No

Answer : Yes

Explanation Yes, the solution meets the goal. The Database Mail is guarded by the database role DatabaseMailUserRole in the msdb database.

Which role grants permissions to perform any action on the server?

Options are :

  • sysadmin (Correct)
  • serveradmin
  • securityadmin
  • processadmin

Answer : sysadmin

Explanation The sysadmin role grants permissions to perform any action on the server. The serveradmin role grants permissions to configure server wide settings and to shut down the server. The securityadmin role grants permissions to manage logins. The processadmin role grants permissions to terminate sessions running on SQL Server instance.

Which security protocol is used to provide security policies such as strong passwords, account locking and password expiration?

Options are :

  • Kerberos (Correct)
  • TCP/IP
  • HTTP
  • None

Answer : Kerberos

Explanation Kerberos: This security protocol is used to provide security policies such as strong passwords, account locking and password expiration. It is supported by the SQL Server over the TCP/IP, named pipes and shared memory communication protocols.

70-764 Administering a SQL Database Infrastructure Exam

Which of the following statement is true?

Options are :

  • The SQL Server Native Access Client provides encrypted authentication for SQL Server logins. (Correct)
  • The SQL Server Native Access Client does not provide encrypted authentication for SQL Server logins.
  • The SQL Server authentication does not require a login when the application is started.

Answer : The SQL Server Native Access Client provides encrypted authentication for SQL Server logins.

Explanation The SQL Server Native Access Client provides encrypted authentication for SQL Server logins. The SQL Server authentication requires a login when the application is started.

True or False: We can use Azure Active Directory to manage user identities for connections to Azure databases in a single place.

Options are :

  • True (Correct)
  • False

Answer : True

Explanation The given statement is true. We can use Azure Active Directory to manage user identities for connections to Azure databases in a single place.

Which of the following statement is used to disable a login?

Options are :

  • ALTER LOGIN Demo DISABLE; (Correct)
  • DISABLE LOGIN Demo;
  • DROP LOGIN Demo;
  • ALTER LOGIN Demo;

Answer : ALTER LOGIN Demo DISABLE;

Explanation ALTER LOGIN Demo DISABLE; statement is used to disable a login. DROP LOGIN Demo; statement is used to remove logins from a server.

70-764 Administering a SQL Database Infrastructure

Which of the following statement identifies and authorizes login tokens?

Options are :

  • SELECT * FROM sys.login_token; (Correct)
  • SELECT * FROM sys.user_token;
  • SELECT * FROM sys.loginuser_token;
  • SELECT * FROM sys.userlogin_token;

Answer : SELECT * FROM sys.login_token;

Explanation SELECT * FROM sys.login_token; statement identifies and authorizes login tokens.

Which role grants permission to manage linked servers?

Options are :

  • dbcreator
  • diskadmin
  • bulkadmin
  • setupadmin (Correct)

Answer : setupadmin

Explanation The setupadmin role grants permission to manage linked servers. The dbcreator role grants permission to manage databases. The diskadmin role grants permission to manage disk files. The bulkadmin role grants permission to execute the BULK INSERT statement.

You have to configure a SQL Server instance to ensure that a user named U1 can send mail by using DatabseMail.

Solution: You add the DatabaseMailUserRole to U1 in the master database. 

Does this solution meet the goal?

Options are :

  • Yes
  • No (Correct)

Answer : No

Explanation No, the solution does not meet the goal. The Database roles or users must be created in the msdb database and must be a member of DatabaseMailUserRole in order to send emails.

70-764 Administering a SQL Database Infrastructure

You have to configure a SQL Server instance to ensure that a user named U1 can send mail by using DatabseMail.

Solution: You add the DatabaseMailUserRole to U1 in the tempdb database.

Does this solution meet the goal?

Options are :

  • Yes
  • No (Correct)

Answer : No

Explanation No, the solution does not meet the goal. The Database roles or users must be created in the msdb database and must be a member of DatabaseMailUserRole in order to send emails.

You have to configure a SQL Server instance to ensure that a user named U1 can send mail by using DatabseMail.

Solution: You add the DatabaseMailUserRole to U1 in the model database. 

Does this solution meet the goal?

Options are :

  • Yes
  • No (Correct)

Answer : No

Explanation No, the solution does not meet the goal. The Database roles or users must be created in the msdb database and must be a member of DatabaseMailUserRole in order to send emails.

Which of the following Fixed Server-Level role should you use to grant permissions for terminating sessions running on SQL Server instance?

Options are :

  • serveradmin
  • processadmin (Correct)
  • dbcreator
  • securityadmin

Answer : processadmin

Explanation You should use processadmin role. It grants permissions to terminate sessions running on the SQL Server instance. dbcreator grant permissions to manage databases. serveradmin grant permissions to configure server-wide settings and to shut down the server. securityadmin grant permissions to manage logins.

70-764 Administering a SQL Database Infrastructure Mock Exam

True or False: We can create user defined server level roles using the CREATE SERVER ROLE statement only.

Options are :

  • True
  • False (Correct)

Answer : False

Explanation We can create user defined server level roles by using the CREATE SERVER ROLE statement or the SSMS GUI.

Which database includes the loginmanager and dbmanager fixed roles in Azure SQL database?

Options are :

  • master (Correct)
  • msdb
  • tempdb

Answer : master

Explanation In Azure SQL database, master includes the loginmanage and dbmanager fixed roles.

You have to grant members of a role permission to view query execution plans. Which permission should you grant?

Options are :

  • SHOWPLAN (Correct)
  • ROLE
  • SELECT
  • EXECUTION

Answer : SHOWPLAN

Explanation You should grant the SHOWPLAN permission, to grant members of a role permission to view query execution plans.

70-764 Administering a SQL Database Infrastructure

You have to remove GRANT and DENY permissions. What should you use?

Options are :

  • REVOKE (Correct)
  • REMOVE
  • GRANT

Answer : REVOKE

Explanation You should use REVOKE. REVOKE: It removes GRANT and DENY permissions.

You are the database administrator for a company. You have to enable a user to change the definition of a stored procedure. Which permission should you use?

Options are :

  • VIEW DEFINITION
  • ALTER (Correct)

Answer : ALTER

Explanation You should use ALTER permission. ALTER permission: It enables a user to change the definition of a stored procedure. VIEW DEFINITION permission: It enables a user to view the code definition of the stored procedure.

Which permission set allows the code to access local and network resources, registry and environment variables?

Options are :

  • SAFE
  • EXTERNAL_ACCESS (Correct)
  • UNSAFE

Answer : EXTERNAL_ACCESS

Explanation EXTERNAL_ACCESS permission set: It allows the code to access local and network resources, registry and environment variables. SAFE permission set: It strictly limits the actions that the assembly can perform and inhibits it from accessing external system resources. UNSAFE permission set: It relaxes many standard controls over code.

70-764 Administering a SQL Database Infrastructure

Which of the following assigns a permission?

Options are :

  • GRANT (Correct)
  • DENY
  • REVOKE

Answer : GRANT

Explanation GRANT: It assigns a permission. DENY: It explicitly denies a permission. REVOKE: It removes both GRANT and DENY permissions.

You handle a Microsoft SQL Server environment. You plan to encrypt data when you create backup. You have to configure the encryption options for backup. What should you configure?

Options are :

  • AES 256 bit key (Correct)
  • DES key
  • certificate
  • MD5 hash

Answer : AES 256 bit key

Explanation For encryption during backup, you must specify an encryption algorithm. Encryption algorithms: Triple DES, AES 128, AES 192, AES 256.

A company named XYZ has an on premises Azure SQL Database instances and SQL Server environment. An environment hosts some customer databases. One customer reports that their database is not responding as fast as the service level agreement dictate.  You examine that the database is fragmented.

You have to optimize query performance.

Solution: You run DBCC CHECKDB command.

Does this solution meet the goal?

Options are :

  • No (Correct)
  • Yes

Answer : No

Explanation No, the solution does not meet the goal. DBCC CHECKDB: It only checks the physical and logical integrity of all the objects in the specified database. It does not improve query performance.

70-764 Administering a SQL Database Infrastructure Mock Exam

A company named XYZ has an on premises Azure SQL Database instances and Microsoft SQL Server environment. An environment hosts some customer databases. One customer reports that their database is not responding as fast as the service level agreement dictate.  You examine that the database is fragmented.

You have to optimize query performance.

Solution: You rebuild all indexes.

Does this solution meet the goal?

Options are :

  • No
  • Yes (Correct)

Answer : Yes

Explanation Yes, the solution meets the goal. You can fix index fragmentation by rebuilding an index or reorganizing an index.

You are DBA for a company named XYZ that hosts SQL Server. You handle both on premises and Azure SQL database environments. Customer connect to database by using business application. The Developer connect by using SSMS. You have to provide permission to a service account that will be used for provision a new database for a customer.

Which permission should you grant?

Options are :

  • db_datawriter
  • sysadmin
  • dbo
  • dbcreator (Correct)

Answer : dbcreator

Explanation You should grant dbcreator permission. dbcreator grant permissions to manage databases. The members of the dbcreator can create, drop, alter and restore any database.

You work for a company named XYZ. You have to assign permissions for multiple columns in one GRANT or DENY statement. Which type of security should you use?

Options are :

  • Column-Level (Correct)
  • Row-Level

Answer : Column-Level

Explanation Column-Level Security: It can assign permissions for multiple columns in one GRANT or DENY statement.

Developing SQL Databases - Exam 70-762 Certification Test

Which of the following Fixed Server-Level role should you use to execute the BULK INSERT statement?

Options are :

  • serveradmin
  • bulkadmin (Correct)
  • dbcreator
  • securityadmin

Answer : bulkadmin

Explanation You should use bulkadmin role. It grants permissions to execute the BULK INSERT statement. The dbcreator grant permissions to manage databases. The serveradmin grant permissions to configure server-wide settings and to shut down the server. The securityadmin grant permissions to manage logins.

Which of the following statement is true?

Options are :

  • We can reset passwords by using SSMS or the ALTER LOGIN statement. (Correct)
  • We can reset passwords by using SSMS only.
  • We can reset passwords by using the ALTER LOGIN statement only.

Answer : We can reset passwords by using SSMS or the ALTER LOGIN statement.

Explanation We can reset passwords by using SSMS or the ALTER LOGIN statement.

You have to examine information about CPU times, logins and Disk I/O on a particular database in Microsoft Azure. What should you use?

Options are :

  • SSDT
  • SQL Server Data Collector
  • Activity Monitor (Correct)
  • SSMS Object Explorer

Answer : Activity Monitor

Explanation Activity Monitor: It displays information about SQL Server processes. SQL Server Data Collector: It is used to get information about missing indexes.

70-764 Administering a SQL Database Infrastructure Exam

True or False: The REVOKE statement revokes both DENY and GRANT statements.

Options are :

  • True (Correct)
  • False

Answer : True

Explanation The REVOKE statement revokes both DENY and GRANT statements.

Which of the following server scoped permissions grants permission to execute DBCC commands that affect the contents of the buffer pool?

Options are :

  • ALTER SERVER STATE (Correct)
  • ALTER ANY LOGIN
  • VIEW SERVER STATE
  • ALTER TRACE

Answer : ALTER SERVER STATE

Explanation ALTER SERVER STATE: It grants permission to execute DBCC commands that affect the contents of the buffer pool. VIEW SERVER STATE: It grants SELECT permissions on server level dynamic management objects. ALTER TRACE: It grants administrative control over SQL Trace.

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions