202 Multiple choice questions
- Restore specific files or filegroups to a database.
- hierarchical structure
- • Overwrite the existing database.
• Preserve the replication settings.
• Prompt before restoring each backup.
• Restrict access to the restored database.
- a granted permission.
- denied permissions
- login side
- Recovers data that is already consistent with the database and needs only to be made available.
- GRANT All ON Contacts TO dbcreator
- CREATE USER [LRN]
FROM LOGIN [WS2008\cbastiao]
- assign or check object permissions.
- database objects
- last backup of the data.
- Can create, alter, drop, and restore databases.
- mixed mode
- may exist.
- tables, stored procedures, and views.
- GRANT Select, Update ON Emails to Guest, LRN
- EXEC sp_addsrvrolemember 'XPS\Lauren', 'sysadmin'
- Windows authentication or SQL Server authentication.
- ALL, SELECT, INSERT, DELETE, REFERENCES, UPDATE, or EXECUTE.
database restore, File restore, Page restore, Piecemeal restore,
Recovery only, Transaction log restore, Create a mirror database, Create
and maintain a standby server.
- any tables.
- affect entire rows.
- DROP ROLE Manager
- Authorizes a user to issue DDL commands (create, alter, drop).
- establishing or confirming a user or system identity.
- specific database functions
- Allows a user to perform backups, checkpoints, and DBCC commands,
but not restores (Only server sysadmins can perform restores.)
- Restore specific pages to a database.
- Granting object permission
- Windows user groups.
- • By adding the role to the user in the user's Database User Properties form (Database » Security » Users » Properties of user )
• By adding the user to the role in the Database Role Properties dialog (Database » Security » Roles » Database Roles » Properties of role)
- fixed roles, the public role, and user-defined roles.
- A user «cannot» read from any table in the database. This overrides any object-level grant.
- access the object.
- RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AventureWorks.BAK'
- the permissions assigned
backup of 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
- server environment.
- Allows good performance while using the least log space.
- Login Properties dialog box.
- views, stored procedures, and user-defined functions.
- rights and permissions
- accessing the object.
- public role
- risk for a security breach.
- Partial backup & Differential partial backup
- last full or incremental backup.
- DROP LOGIN command
the full or bulk-logged recovery model, since the logs record each
transaction, restoring from log backups is required to reach a desired
perform any activity in the SQL Server installation, regardless of any
other permission setting. The sysadmin role even overrides denied
permissions on an object.
- granting, revoking, or denying user login permissions.
- individually, through a standard database role, and through the public role
- File backup & Differential file backups
- transactions that are running;
- not actually created
- • Simple Recovery
• Full Recovery
- full backups and differential backups.
- Restore an entire database from a full database backup.
- Grant and Deny object permissions.
- properties of the object
- recognize them.
- backup devices.
- database side.
- differential base.
- predefined server roles
- security identifier
- GRANT Select ON Emails TO Joe
- The right to delete existing data.
- Grant permission.
- database snapshot.
- perform certain server-related administrative tasks.
- all permissions in the database.
- fixed server role.
- A backup that contains only the data that were modified since the most recent partial backup of the same set of filegroups.
- sysadmin server role
- any user in that group.
- The right to create foreign keys.
- properties of the user.
a file or filegroup in a multi-filegroup database. After a full file
restore, a differential file backup can be restored.
- valid Windows account
- user-defined roles.
an entire database, beginning with a full database backup, which may be
followed by restoring a differential database backup (and log backups).
- A full backup of all the data in one or more files or filegroups.
backup of all files in the database. This backup contains only the data
that were modified since the most recent database backup of each file.
- a baseline or minimum permission level.
- Local user account, Local system account, Domain user account.
- Can configure linked servers, extended stored procedures, and the startup stored procedure.
- Active Directory or Windows accounts.
- sysadmin role
- SQL authentication.
- database is created;
- New User Context Menu command
- Deny permission
- Can perform bulk insert operations.
- • Windows user login
• Membership in a Windows user group
• SQL Server-specific login (if the server uses mixed-mode security)
- Restores a database in stages, beginning with the primary filegroup and one or more secondary filegroups.
- full access to every server function, database, and object
- Contains all the data in a specific database or set of filegroups or files to allow recovering that data.
- user account
- A backup of one or more files that contain data extents that were changed since the most recent full backup of each file.
- full backup must be created
- Windows account
- server login name.
- CREATE ROLE Manager
- When information is written to one server, it is automatically replicated to the second server.
- Database backup & Differential database backups
- least administration
- Login Properties form
- Securables page
a user to read all data in the database. This role is the equivalent of
a grant on all objects, and it can be overridden by a deny permission.
- When the active server fails, the passive server will become the active server, allowing for minimum downtime.
- the server and database roles.
»» database »» open the object to manage (tables, views, stored
procedure, functions) »» Properties »» Click the Permissions page »» add
user/role »» Select the appropriate Grant to Deny permission.
- backward compatibility.
- Restore part of a database.
- Control Panel > Administrative Tools > Computer Management.
- Can manage the logins for the server.
- username and password.
- Restore a transaction log onto a database.
- Permits a user to manage database-level security - including roles and
- the sysadmin role to take effect.
- CREATE LOGIN [WS2008\CBASTIAO] FROM WINDOWS
- 1. SSMS »» expand the database folder »» Security folder »» Roles »» Database Roles folder.
2. Double-click the appropriate role to open the Database Role Properties dialog box.
3. Add or remove users from the role.
- modified as desired after installation.
- database objects
- application to gain access regardless of the user.
is a special role that has all permissions in the database. This role
includes all the capabilities of the other roles and differs from the
dbo user role. This is not the database-level equivalent of the server
sysadmin role because an object-level deny will override membership in
- DROP USER userTeste
- restore to a point in time since the logs files record all SQL transactions and the time they were performed.
- Blocks a user from modifying data in any table in the database. This overrides any object-level grant.
- a logical representation of a person within an electronic system.
- the last full backup.
- Restores individual pages.
Databases »» Right-click the database, point to Tasks, then click
Restore »» Database »» the name of the restoring database appears in the
To database list box. To create a new database, enter its name in the
list box (etc)
- Can create, alter, and drop disk files.
- the guest user account has been created.
- login names
- to have something to restore if data is lost during a business's daily routine.
- database security
- The right to modify existing data. Update rights for which a WHERE clause is
used require select rights as well. Update permission can be set on specific columns.
- GRANT Permission, Permission
TO User/role, User/role
WITH GRANT OPTION
a user to write to all data in the database. This role is the
equivalent of a grant on all objects, and it can be overridden by a deny
- CREATE ROLE auditors AUTHORIZATION db_securityadmin;
- any user database.
- Authorizes a user to access the database, but not to manage database level security.
- Latest full backup of the data
- application role
- GRANT, REVOKE, and DENY.
- production workloads.
- can grow a lot.
- User Mapping page
- The right to select data. Select permission can be applied to specific columns.
- full backup is restored first
- The right to insert data.
- REVOKE, and DENY.
- CREATE LOGIN command.
- denied or a granted
process by which individual access to a computer system is controlled
by identification of the user through the credentials he or she
- A full backup of the whole database.
- • Restore an entire database from a full database backup.
• Restore part of a database.
• Restore specific files or filegroups to a database.
• Restore specific pages to a database.
• Restore a transaction log onto a database.
• Revert a database to the point in time.
- RESTORE DATABASE name_of_database FROM DISK = 'name of backup'
- more than what they need to.
- automatically granted the privileges of the guest user, as long as the guest user account has been created.
- EXEC sp_dropRoleMember Manager, Joe
- Can kill a running SQL Server process.
- EXEC sp_addrolemember Manager, Joe
- The right to execute stored procedures or user-defined functions.
- Active Directory Users and Computers snap-in.
- Can configure the server-wide settings, including setting up full-text searches and shutting down the server.
- Full, differential, and incremental backups.
- Select, Insert, Update, Delete, DRI (References), Execute
- Differential backup
- permission to objects
- mirror database
- restore plan.
- used by several successive differential backups.