Database - Lesson 5 Review

What fixed database role allows you to have full permission to a database? a) sa b) db_owner c) db_datareader d) db_accessadmin

db_owner - this role has all permissions in the database. This role includes all the capabilities of the other roles. It is different from the dbo user role. This is not the database-level equivalent of the server sysadmin role; an object-level deny will override membership in this role.

If you are using mixed mode, what account is the build-in SQL administrator account? a) administrator b) sysadmin c) root d) sa

sa - this is the built-in SQL administrator account associated with SQL authentication; less secure than Windows logins, so avoiding mixed mode is recommended.

What fixed server role allows you to create, alter, and drop disk files? a) bulkadmin b) dbcreator c) processadmin d) diskadmin

Diskadmin - this role can create, alter, and drop disk files.

What is used to grant a user account access to a database? a) permission b) right c) password d) encryption key

A permission is used to grant an entity, such as a user, access to an object, such as another user or a database.

What is the act of establishing or confirming a user or system identity? a) authentication b) authorization c) auditing d) logging

Authentication - this is the act of establishing or confirming a user or system identity. Windows Authentication mode is superior to mixed mode because users don't need to learn yet another password and because it leverages the security design of the network.

18. What command is used to create a user called server\user1?

CREATE USER 'server\user1' This command must be issued from within the database to which the user is to be granted access. The first parameter is the server login, and the second is the optional database username.

What command would you use to remove a user from a SQL server?

DROP LOGIN - To create a login using Transact-SQL syntax so that you can add a Windows user or group, run the CREATE LOGIN command; Ex: CREATE LOGIN 'XPS\Joe'.

17. Which fixed server roles can create, alter, and drop databases?

Dbcreator - the role can create, alter, drop, and restore databases.

Which service account should you use if you need to have network access? a) local user account b) local system account c) domain user account d) guest account

Domain User Account - this is the recommended login account because SQL Server can use the Windows account specifically created for the SQL Server. You can then grant administrator rights to the SQL Server account for which you can then interact with other servers on the network.

What command would you use to grant object permissions? a) GRANT b) USE c) RUNAS d) ALLOW

Grant - the Transact-SQL GRANT statement grants permissions on a table, view, table-valued function, stored procedure, extended stored procedure, scalar function, aggregate function, service queue, or synonym.

Which type of backup backs up all changes since the last backup? a) full backup b) differential backup c) incremental backup d) copy backup

Incremental Backup - this is based on the last backup of data. An incremental backup contains only the data that has changed since the last full or incremental backup.

Which type of backup backs up only the data in the primary filegroup and specified files or filegroups? a) full backup b) differential backup c) partial backup d) incremental backup

Partial Backup - this backs up all the full data in the primary filegroup, every read/write filegroup, and any optionally specified read-only files or filegroups. A partial backup of a read-only database contains only the primary filegroup.

16. A ____________________ restores the database in stages.

Piecemeal Restore - this restores the database in stages, beginning with the primary filegroup and one or more secondary filegroups.

What role is automatically granted to all users? a) sysadmin b) root c) public d) standard

Public - all users are automatically a member of the public standard database role.

If you need to restore a database after an incremental backup, what do you need to do for a restore? a) Restore the full backup and the first incremental backup. b) Restore the full backup and the last incremental backup. c) Restore the full backup and all the incremental backups since the last full backup. d) Restore the last incremental backup.

Restore the full backup and all the incremental backups since the last full backup; at restore time, the full backup is restored first, followed by each incremental backup following the full backup.

What permission would you assign that will remove the permission previously assigned? a) grant b) denied c) revoke d) reset

Revoke -each object's permission is assigned through either granting, revoking, or denying user login permissions. The revoke permission removes the permission assigned, regardless of whether the permission is a deny permission or whether it's a grant permission.

What fixed role allows you to create logins for the server? a) dbcreator b) diskadmin c) securityadmin d) serveradmin

Securityadmin - this role can manage the logins for the server.

What role can you give to a user who gives full access to the SQL server and databases? a) root b) system c) sysadmin d) local

Sysadmin - If you add a user to the sysadmin role, that user will now have full access to every server function, database, and object for that server. With that full access, a user can then grant permission to other users against other server securables.

In mixed mode, what two types of logins are allowed?

Windows logins and SQL logins - SQL servers support mixed mode, which allows you to connect to the SQL server using Windows authentication or SQL Server authentication.

What fixed database role would you use to manage database-level security roles and permissions? a) sysadmin b) db_securityadmin c) db_accessadmin d) db_datareader

db_securityadmin - This role can manage database-level security (roles and permissions).