Permissions
You will need to be to identify those permissions which are reserved for the SA only,
those which are dbo only, those which are transferable to other logins, group permissions,
chains of permissions, what permissions can be assigned for a database, table, view, and
procedure. You'll also need to know how impersonation works and how that affects
permissions and objects within the database. You need know which logins can impersonate
other users.Devices
How to create a database device and resizing restrictions for a device
Databases
Know how to change database ownership. Understand what each of the database options are
and what they allow you to do. Understand the create database syntax and all of the
associated options. Know that databases are created in increments of megabytes. The
default database size for new databases is a server level configuration option. Also
understand the following: a data page is 2K size, an extent is 8 data pages, an allocation
unit is 32 extents, and a database is 2 allocation units. Each table that is created is
allocated its own extent. A single extent can not contain multiple tables. This means that
if you create a database that is two megabytes in size, that database can be filled up
simply by creating 128 tables without ever adding a single row of data.
Backup and restore
The backup and restore questions run the gamut of all of the commands and options you will
encounter.
1. Syntax for dump and load of a database or transaction log
2. Syntax for creating and dropping a backup device
3. What types of backup devices can be created
4. What options are available for the dump transaction statement and what do each of these
mean
5. What commands or procedures should be run before and after a backup to insure the
integrity of the backup
6. What option is used with the create database statement to enable the immediate
reloading of a database from a backup
7. How database options and minimally logged commands such as truncate, select into, and
BCP effect your ability to do transaction log dumps
8. The syntax for disk reinit and disk refit and what role these two commands play in
recovering a database
SQL Executive
Know the SQL Executive service runs all of the tasks and alerts in your database. Also
understand how it utilizes the msdb database.
Alerts
Know which components are required to enable alerting within SQL server and how you
configure alerts.
MSDB
Understand the function of this database, main tables contained in this database, and what
their purpose is.
Security modes
List the three security modes available to SQL server and how these affect your
installation. Also know which NT accounts automatically map to which SQL Server accounts
when running under integrated security.
70-026 SQL Server Administration 1 2 3
