Managing SQL SVR Security Flashcards
Wide World Importers has a Windows Server 2003 domain and all the servers running SQL Server are running on Windows Server 2003 Enterprise Edition. The SQL Server instance is configured for Windows-only authentication. Database roles have been created for each group of permissions within a database. Logins are added to the database roles. The DBAs want to move the security assignment of users to the owners of each application without giving up control of the accounts or permissions inside the SQL Server instance. How can the DBAs accomplish their goals? (Choose two. Each answer represents part of the solution.)
- Have the Windows administrator allow application owners to manage the Windows groups associated to their applications.
- Add the logins for application owners to the sysadmin role.
- Add the logins for application owners to the securityadmin role.
- Map SQL Server logins to the Windows group corresponding to each application.
- Have the Windows administrator allow application owners to manage the Windows groups associated to their applications.
- Map SQL Server logins to the Windows group corresponding to each application.
EXPLANATION:
You should map SQL Server logins to each Windows group corresponding to an application, add the login as a user to the appropriate database, and then add the user to the appropriate database role. After you complete these steps, all the application owners need is the ability to manage the appropriate Windows group or groups to meet your needs.
Exam Objective:
Managing SQL Server Security
Exam SubObjective(s):
Manage logins and server roles.
Tina needs to be able to back up databases on an instance without also having the authority to restore or access the contents of the database. How would you accomplish this business requirement with the least amount of effort?
- Add Tina to the sysadmin role.
- Add Tina to the db_backupoperator role.
- Add Tina to the db_owner role.
- Add Tina to the diskadmin role.
Add Tina to the db_backupoperator role.
EXPLANATION:
Members of the db_backupoperator role can back up the database, but they are not allowed to restore a database or access any objects within the database.
Exam Objective:
Managing SQL Server Security
Exam SubObjective(s): Manage users and database roles.
Wide World Importers has just implemented a new order inquiry system. All users with access to the database need to be able to issue a SELECT statement against any table within the database. How can you accomplish this functionality with the least amount of effort?
- Grant the users SELECT permission on the database.
- Grant the users SELECT permission on every table in the database.
- Grant the users SELECT permission on every schema in the database.
- Add the users to the db_datawriter database role.
Grant the users SELECT permission on the database.
EXPLANATION:
When you grant SELECT permission on the database, the user is able to issue a SELECT statement against any table within the database.
Exam Objective:
Managing SQL Server Security
Exam SubObjective(s): Manage database permission
Which statement prevents users from viewing metadata about objects in a single database, even if the user has access to the objects?
- DENY VIEW DEFINITION
- DENY VIEW SERVER STATE
- DENY VIEW ANY DEFINITION
- REVOKE VIEW DEFINITION
DENY VIEW DEFINITION
EXPLANATION:
DENY VIEW DEFINTION prevents a user from viewing object metadata to which the user would otherwise have access.
Exam Objective:
Managing SQL Server Security
Exam SubObjective(s): Manage schema permissions and object permissions.
The Human Resources (HR) director at Contoso needs to ensure that only authorized users are accessing employee pay records. What do you need to implement to satisfy these auditing needs?
- A DML trigger
- Server audit specification
- Database audit specification
- A DDL trigger
Database audit specification
EXPLANATION:
You can create a database audit specification to log any SELECT, INSERT, UPDATE, DELETE, BCP, or BULK INSERT statements executed against the employee pay records table for just the group of users who have access
Exam Objective:
Managing SQL Server Security
Exam SubObjective(s): Audit SQL Server instances.
The database administrators at Fabrikam have implemented log shipping for the Orders database. To ensure that log shipping cannot break, you need to prevent anyone from changing the recovery model of the database to SIMPLE. How can you accomplish this task?
- A DML trigger.
- Server audit specification.
- You can’t prevent the change of the recovery model.
- A DDL trigger.
You can’t prevent the change of the recovery model.
EXPLANATION:
Because ALTER DATABASE can make changes to the file system, which is nontransactional, you cannot prevent ALTER DATABASE from executing. Therefore, the requirement to prevent the change is not possible to implement.
Exam Objective:
Managing SQL Server Security
Exam SubObjective(s): Audit SQL Server instances.
The DBAs at Woodgrove Bank manage several sensitive databases containing credit card and customer information. They need to encrypt the entire contents of the database so that an attacker cannot read information off the disk. How can they meet their requirement with the least amount of effort?
- Create a symmetric key in the database that is used to encrypt the data.
- Create a certificate in the database that is used to encrypt the data.
- Create a database encryption key and enable the database for encryption.
- Create an asymmetric key in the database that is used to encrypt the data.
Create a database encryption key and enable the database for encryption.
EXPLANATION:
TDE uses a certificate in the master database that is employed to encrypt a database encryption key. After you enable it, SQL Server encrypts data on the disk so that it cannot be read by an attacker, without requiring any changes to applications.
Exam Objective:
Managing SQL Server Security
Exam SubObjective(s): Manage transparent data encryption.
The DBAs at Woodgrove Bank manage several sensitive databases containing credit card and customer information. Due to recent data thefts at other banks that have made headlines, the business wants to ensure that all data within backups is encrypted. How can they accomplish the encryption requirement without needing to change applications?
- Create a certificate in the database that is used to encrypt the data.
- Create an asymmetric key in the database that is used to encrypt the data.
- Create a symmetric key in the database that is used to encrypt the data.
- Create a database encryption key and enable the database for encryption.
Create a database encryption key and enable the database for encryption.
EXPLANATION:
TDE uses a certificate in the master database that is employed to encrypt a database encryption key. After it’s enabled, SQL Server encrypts data on the disk so that it cannot be read by an attacker without requiring any changes to applications.
Exam Objective:
Managing SQL Server Security
Exam SubObjective(s): Manage transparent data encryption.
You are the database administrator at A. Datum Corporation. Users are complaining that applications cannot connect to the SQL Server. You have verified all the application settings and you can connect to the server from your desktop using SSMS. But the users’ applications keep returning an “Access denied” error message. What could be the problem?
- Remote connections are not enabled.
- The TCP endpoint for TSQL is STOPPED.
- Users do not have CONNECT permissions on the endpoint.
- The TCP endpoint for TSQL is DISABLED.
Users do not have CONNECT permissions on the endpoint.
EXPLANATION:
If users do not have CONNECT permission on the TCP endpoint for TSQL, they receive an “Access denied” error message.
Exam Objective:
Managing SQL Server Security
Exam SubObjective(s): Configure surface area.
You have configured a Database Mirroring session within your environment. The Principal and Mirror endpoints were created successfully with a ROLE setting of PARTNER and then started. You have verified that you can connect to and authenticate to each endpoint. However, Database Mirroring fails to configure properly. What might be the problem?
- The authentication mode is set to NEGOTIATE.
- The encryption setting is different on each endpoint.
- The encryption is set to AES on each endpoint.
- The authentication mode is set to NTLM.
The encryption setting is different on each endpoint.
EXPLANATION:
If you have verified the connection and authentication, only a mismatch of encryption settings prevents Database Mirroring from configuring.
Exam Objective:
Managing SQL Server Security
Exam SubObjective(s): Configure surface area.
Which tool would you use to enable or disable SQL Server features?
- SQL Server Configuration Manager
- The sp_configure tool
- SQL Server Surface Area Configuration Manager
- SQL Server Installation Center
The sp_configure tool
EXPLANATION:
The sp_configure tool is used to enable and disable features.
Exam Objective:
Managing SQL Server Security
Exam SubObjective(s): Configure surface area.
