Start up

1
Start up

• Log on to the network
• Start Management Studio
• Connect to Reliant\sql2k5 and your SalesOrders
  database
• Start Books Online

2
MIS 431Dr. Steve RossSpring 2007

• Security, Recovery,
• and Data Transfer

Material for this lecture is drawn from SQL
Server 2005 Applied Techniques, and the
professors experience.
3
Security

• Enabling remote access
• Authentication
• Principal person, group, or process
• Operating system, server, database levels
• Indivisible or collection
• Authentication mode
• Windows authentication mode
• Requires trusted connection
• Mixed authentication mode

4
Creating a SQL Server Login

• Must specify
• Login name
• Password
• Default DB
• Server role (if any)
• Database role

5
Server Roles (Fixed)

• bulkadmin
• dbcreator
• diskadmin
• processadmin
• securityadmin
• serveradmin
• setupadmin
• sysadmin

See Table 2-2 and Books Online for descriptions
6
Database Roles (Default)

• db_accessadmin
• db_backupoperator
• db_datareader
• db_datawriter
• db_ddladmin
• db_denydatareader
• db_denydatawriter
• db_owner
• db_securityadmin
• public

See Table 2-3 and Books Online for descriptions
7
Application Roles

• Allow access to specific data only to users that
  connect through a specific application

8
Schemas

• Container objects that allow you to group
  database objects
• Default schema is dbo or the login of the
  object creator
• Fully qualified name
• server.database.schema.object.field
• Separates user from object ownership

9
Table and Column Permissions

• Table permissions
• alter
• control
• delete
• insert
• references
• select
• take ownership
• update
• view definition

• Column permissions
• select
• update
• reference

See Tables 2-4, 2-5, and Books Online for
descriptions
10
Programmable Objects Permissions

• Stored procedures
• alter
• control
• execute
• take ownership
• view definition

• User-defined functions
• take ownership
• view definition
• select (table-valued functions)
• execute (scalar functions)

See Tables 2-6, 2-7, and Books Online for
descriptions
11
Ownership Chains

• The sequence of database objects accessing each
  other
• Granting permission to execute or select a
  programmable object also grants permission to
  select or update a referenced object if they have
  the same owner

12
Creating a Login
13
Creating a Login
14
Creating a Login
15
Creating a Login
16
Creating a Login
17
Practical Exercise 5

• In the sql2k5 instance of SQL Server
• Add logins for each employee
• Ann, Mary, Thomas, Janet, John, David, Peter,
  and Susan
• The login name should be your initials employee
  first name e.g., SCRAnn
• SQL Server authentication
• Grant public access to each login to your
  database.

18
Determining User ID
?
?
?
19
Practical Exercise 6

• In your database
• Create a table (UserIDs) to hold SQL user
  identification numbers and person identification
  numbers.
• Person ID numbers are a foreign key to Employees
• SQL user ID numbers match the uid in sysusers
• Neither field can be null
• Both ID numbers are unique (within your database)
• A person might have more than one SQL user ID
• Populate your table

20
Practical Exercise 6 (contd)

• Update your ER Diagram in SQL Server
• Refresh your ER Diagram in Visio

21
Disaster Recovery Backup

• Recovery Model
• Simple
• Use with Full or Differential Backups
• Might not recover most recent transactions
• Full
• Use with Transaction Log Backups
• Allows recovery of all committed transactions

22
Disaster Recovery Backup

• Backup Device
• Tape
• Less common
• Disk
• Physical or logical device
• Whenever possible, not the same device containing
  database files

23
Disaster Recovery Backup

• Backup type
• Full
• Differential
• Transaction log
• If disaster occurs atthen recover either F1
  D2or F1 TL1 TL2

24
Disaster Recovery Restore

• Restore last full backup
• Then, either
• Last differential backup
• or
• All transaction log backups
• since the last full backup

25
Backup Your Database

• Right-clickyour db, then choose

Slide added 5/23/07
26
Backup Your Database

• TypicalGeneralchoices

Slide added 5/23/07
27
Backup Your Database

• TypicalOptionschoices
• After allchoiceshave been,click OK

Slide added 5/23/07
28
The MaintenancePlan Wizard

• Under Management,right-clickMaintenance Plans
• Choose MaintenancePlan Wizard

Slide added 5/23/07
29
The Maintenance Plan Wizard
Slide added 5/23/07
30
The Maintenance Plan Wizard
Slide added 5/23/07
31
The MaintenancePlan Wizard
Slide added 5/23/07
32
The Maintenance Plan Wizard
Slide added 5/23/07
33
The Maintenance Plan Wizard

• Click through several OK and Finish buttons

Slide added 5/23/07
34
Transferring Data

• Backup and restore
• Detach and attach
• Replication
• SQL Server Integration Services (SSIS)

35
Next Lecture

• Multiple Tables I Inner Joins