Learn efficiently and remember over time.
Start Long-Term Learning
Add this set to a folder
The purpose of a database backup is ___
to have something to restore if data is lost during a business's daily routine.
SQL Server offers three recovery models for each database. They are: ___
• Simple Recovery
• Full Recovery
Simple Recovery requires the ___ since the transaction log backups are truncated on a regular basis.
Full Recovery allows you to ___
restore to a point in time since the logs files record all SQL transactions and the time they were performed.
Full backup: ___
Contains all the data in a specific database or set of filegroups or files to allow recovering that data.
At restore time, the full backup is restored ___, followed by the most recent differential backup.
An incremental backup contains only the data that has changed since the ___
last full or incremental backup.
At restore time, the ___ , followed by each incremental backup following the full backup.
full backup is restored first
Over time, as a database is updated, the amount of data that is included in differential backups increases. This makes the backup slower to create and to restore. Eventually, another ___ to provide a new differential base for another series of differential backups.
full backup must be created
Types of database backups supported by SQL Server: ___
Database backup & Differential database backups
Differential database backups ???
A 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.
Partial backup ???
A 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 filegroup.
Differential partial backup ???
A backup that contains only the data that were modified since the most recent partial backup of the same set of filegroups.
Differential file backups ???
A backup of one or more files that contain data extents that were changed since the most recent full backup of each file.
You can ___ new backups to any existing backups on a device, or you can overwrite any existing backups.
You can append new backups to any existing backups on a device, or you can ___ any existing backups.
During a backup operation, SQL Server copies the data directly from the database files to the ___
Restore scenarios possible in SQL Server include the following: ___
Complete database restore, File restore, Page restore, Piecemeal restore, Recovery only, Transaction log restore, Create a mirror database, Create and maintain a standby server.
Complete database restore ???
Restores an entire database, beginning with a full database backup, which may be followed by restoring a differential database backup (and log backups).
File restore ???
Restores a file or filegroup in a multi-filegroup database. After a full file restore, a differential file backup can be restored.
Piecemeal restore ???
Restores a database in stages, beginning with the primary filegroup and one or more secondary filegroups.
Recovery only ???
Recovers data that is already consistent with the database and needs only to be made available.
Transaction log restore ???
Under the full or bulk-logged recovery model, since the logs record each transaction, restoring from log backups is required to reach a desired recovery point.
Create a mirror database ???
When information is written to one server, it is automatically replicated to the second server.
Create and maintain a standby server ???
When the active server fails, the passive server will become the active server, allowing for minimum downtime.
When you have a ___ server, you are using an active-passive cluster that consists of two or more servers.
To restore data through the graphical interface tool, follow these steps: ___
Expand 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)
In the Restore options panel, you can choose any of the following options : ___
• Overwrite the existing database.
• Preserve the replication settings.
• Prompt before restoring each backup.
• Restrict access to the restored database.
The Transact-SQL RESTORE command enables you to perform the following restore scenarios: ___
• 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.
To restore the AdventureWorks database using the C:\AdventureWorks.BAK backup, you would execute the following command: ___
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AventureWorks.BAK'
Generically, to restore the database using the specified file, you would execute the following command: ___
RESTORE DATABASE name_of_database FROM DISK = 'name of backup'
A login or logon is ___
the process by which individual access to a computer system is controlled by identification of the user through the credentials he or she provides.
Windows Authentication mode is superior to ___ because users don't need to learn yet another password and because it leverages the security design of the network.
SQL Server includes fixed, predefined server roles. Primarily, these roles grant permission to ___
perform certain server-related administrative tasks.
SQL Server includes ___ . Primarily, these roles grant permission to perform certain server-related administrative tasks.
predefined server roles
The sysadmin role can perform any ___ in the SQL Server installation, regardless of any other permission setting. The sysadmin role even overrides denied permissions on an object.
The sysadmin role can perform any activity in the SQL Server installation, regardless of any other permission setting. The sysadmin role even overrides ___ on an object.
The ___ can perform any activity in the SQL Server installation, regardless of any other permission setting. The ___ even overrides denied permissions on an object.
The ___ is a fixed role, but it can have object permissions like a standard role. Every user is automatically a member of the ___ and cannot be removed, so the ____ serves as a baseline or minimum permission level.
The public role is a fixed role, but it can have object permissions like a standard role. Every user is automatically a member of the public role and cannot be removed, so the public role serves as ___
a baseline or minimum permission level.
An ___ is a database-specific role intended to allow an application to gain access regardless of the user.
An application role is a database-specific role intended to allow an ___
application to gain access regardless of the user.
Regardless of an organization's size, the end result of its ___ should be to ensure that users' assigned rights and responsibilities are enforced through a security plan.
It is important to be aware of the ___ associated with each object in a database because it's possible to inadvertently grant administrative rights to objects or users that should not have them.
rights and permissions
Within SQL Server, users are assigned to roles, which may in turn grant permission to objects... each object has an owner, and ___ also affects permissions.
Within SQL Server, users are assigned to ___, which may in turn grant permission to objects... each object has an owner, and ownership also affects permissions.
Within SQL Server, users are assigned to roles, which may in turn ___ permission to objects... each object has an owner, and ownership also affects permissions.
Within SQL Server, users are assigned to roles, which may in turn grant ___ ... each object has an owner, and ownership also affects permissions.
permission to objects
In the security model for a SQL Server, there are three different methods by which a user can be initially identified: ___
• Windows user login
• Membership in a Windows user group
• SQL Server-specific login (if the server uses mixed-mode security)
At the SQL Server level, where the database resides, users are known by their ___ . This can be a SQL Server login, a Windows domain login, or a username login.
if you add a user to the sysadmin role, that user now has ___ for that server.
full access to every server function, database, and object
The sysadmin role is a powerful one, and you must be sure not to grant it to the ___ user login.
It is possible for users who have not been granted direct access to a database to gain access using the ___ user account - and with this account, they can make limited changes within the database server.
Even though a user may belong to a fixed database role and have certain administrative level permissions, he or she still cannot access data without first being granted permission to ___ (e.g., tables, stored procedures, views, functions).
Each object's permission is assigned through ___ , denying, or revoking user login permissions.
Each object's permission is assigned through granting, ___ , or revoking user login permissions.
Each object's permission is assigned through granting, denying, or ___ user login permissions.
Revoking a permission ___ the permission that has been assigned, regardless of whether it was a denied permission or a granted permission.
Revoking a permission removes the permission that has been assigned, regardless of whether it was a ___ permission.
denied or a granted
A user may have multiple permission ___ to an object (e.g., individually, through a standard database role, and through the public role). If any of these ___ are denied, then the user is blocked from accessing the object.
A user may have multiple permission paths to an object (e.g., ___ ). If any of these paths are denied, then the user is blocked from accessing the object.
individually, through a standard database role, and through the public role
A user may have multiple permission paths to an object (e.g., individually, through a standard database role, and through the public role). If any of these paths are denied, then the user is blocked from ___
accessing the object.
Because SQL Server is an environment within the Windows Server system, one of your primary security concerns should be ensuring that the Windows Server itself is ___
Three different types of accounts are available for the SQL Server service account: ___
Local user account, Local system account, Domain user account.
Domain user account is the recommended login account because the SQL Server can then use the ___ specifically created for it.
Windows Authentication is very robust in that it will authenticate not only Windows users, but also users within ___
Windows user groups.
Using Windows Authentication means that users must have a ___ in order to be recognized by SQL Server
valid Windows account
Access, roles, and permissions can be assigned to the Windows user group, and they will apply to ___
any user in that group.
Local users can be managed by selecting ___
Control Panel > Administrative Tools > Computer Management.
To create a login using Transact-SQL syntax so that you can add a Windows user or group run the ___
CREATE LOGIN command.
Transact-SQL syntax so that you can add the windows username: cbastiao from domain name: WS2008
CREATE LOGIN [WS2008\CBASTIAO] FROM WINDOWS
A Windows login can be removed from SQL Server through SSMS...this doesn't
delete the user from ___ ; it only removes the user from SQL Server.
SQL servers also support mixed mode, which allows you to connect to a SQL server using ___
Windows authentication or SQL Server authentication.
A SQL Server login account and related passwords are defined on the SQL server and are not related to ___
Active Directory or Windows accounts.
Because SQL Authentication is less secure than Windows logins, avoiding mixed mode is ___ ; however, it is available for backward compatibility.
Because SQL Authentication is less secure than Windows logins, avoiding mixed mode is recommended; however, it is available for ___
There are three kinds of database server roles: ___
fixed roles, the public role, and user-defined roles.
There are three kinds of database server roles: fixed roles, the public role, and ___
Fixed server role: Serveradmin ???
Can configure the server-wide settings, including setting up full-text searches and shutting down the server.
Fixed server role: Setupadmin ???
Can configure linked servers, extended stored procedures, and the startup stored procedure.
Fixed server role: Sysadmin ???
Can perform any activity in the SQL Server installation, regardless of any other permission setting. The sysadmin role even overrides denied permissions on an object.
The one user that SQL Server automatically creates during installation of the software is ___
If you add a user to the sysadmin role group, that user must reconnect to the SQL Server instance in order for the full capabilities of ___
the sysadmin role to take effect.
the following code adds the user login ''XPS\Lauren'' to the sysadmin role: ___
EXEC sp_addsrvrolemember 'XPS\Lauren', 'sysadmin'
user-defined roles are typically employed for users who need to perform ___ but to whom you don't want to grant a role that would permit them do more than what they need to.
specific database functions
user-defined roles are typically employed for users who need to perform specific database functions but to whom you don't want to grant a role that would permit them do ___
more than what they need to.
To grant access to a database from the login side using Object Explorer, use the ___ of the Login Properties form.
User Mapping page
To grant access to a database from the login side using Object Explorer, use the User Mapping page of the ___.
Login Properties form
To grant access from the database point of view, use the ___ under the Database > Security > Users node to open the Database User-New form.
New User Context Menu command
A Transact-SQL command to grant database access to a user: ___
CREATE USER [LRN]
FROM LOGIN [WS2008\cbastiao]
To remove Lauren's database access, the system-stored procedure DROP USER requires her database username, not her ___
server login name.
Any user who wishes to access a database but who has not been declared a user within the database is automatically granted the privileges of the guest user, as long as ___
the guest user account has been created.
Any user who wishes to access a database but who has not been declared a user within the database is ___
automatically granted the privileges of the guest user, as long as the guest user account has been created.
Guest users must be removed from a database when they are no longer welcome, as they are a ___
risk for a security breach.
Users may be assigned to multiple roles, so multiple security paths from a user to an object ___
In SQL Server, «fixed database roles» include the following:
fixed database role: «db_accessadmin» ???
Authorizes a user to access the database, but not to manage database level security.
fixed database role: «db_backupoperator» ???
Allows a user to perform backups, checkpoints, and DBCC commands,
but not restores (Only server sysadmins can perform restores.)
fixed database role: «db_datareader» ???
Authorizes 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.
fixed database role: «db_datawriter» ???
Allows 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 permission.
fixed database role: «db_ddladmin» ???
Authorizes a user to issue DDL commands (create, alter, drop).
fixed database role: «db_denydatareader» ???
A user «cannot» read from any table in the database. This overrides any object-level grant.
fixed database role: «db_denydatawriter» ???
Blocks a user from modifying data in any table in the database. This overrides any object-level grant.
fixed database role: «db_owner» ???
This 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 this role.
fixed database role: «db_securityadmin» ???
Permits a user to manage database-level security - including roles and
«Fixed database roles» can be assigned via SSMS using either of the following procedures: ___
• 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)
In Transact-SQL code, you can add a user to a fixed database role by using the ___ system stored procedure.
Creates the «database role» AUDITORS, which is owned by the «db_securityadmin» «fixed database role»:
CREATE ROLE auditors AUTHORIZATION db_securityadmin;
To add a user to a «database role», follow these steps:
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.
Object permissions are permissions that allow a user to act on ___ , such as tables, stored procedures, and views.
Object permissions are permissions that allow a user to act on database objects, such as ___
tables, stored procedures, and views.
Several specific types of object permissions exist: ___
Select, Insert, Update, Delete, DRI (References), Execute
Object permission: Select ???
The right to select data. Select permission can be applied to specific columns.
Object permission: Update ???
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.
If a user does not have the ___ , the highest level object permission would be the Grant and Deny object permissions.
sysadmin server role
If a user does not have the sysadmin server role, the highest level object permission would be the ___
Grant and Deny object permissions.
Steps to modify an object's permissions: ___
SSMS »» 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.
Only select and update permissions can be set at the column level, because inserts
and deletes ___
affect entire rows.
Instead of granting the permission to a user from the properties of the object, you can also grant permissions to an object from the ___
properties of the user.
Instead of granting the permission to a user from the ___ , you can also grant permissions to an object from the properties of the user.
properties of the object
The Transact-SQL statement to provide permission to an object for a specific user and his or her role is as follows:
GRANT Permission, Permission
TO User/role, User/role
WITH GRANT OPTION
The following code grants select permission to Joe for the Emails table:
GRANT Select ON Emails TO Joe
Grants all permissions to the «public role» for the Contacts table:
GRANT All ON Contacts TO dbcreator
The following code grants select and update permission to the guest user and to LRN:
GRANT Select, Update ON Emails to Guest, LRN
The counterpart to creating a role is removing it. A role may not be dropped if any users are currently ___ to it.
A particularly powerful permissions organization technique is to design a ___ of standard database roles.