Transcript Introduction to ASP.NET

Sql Server Advanced Features
MIS 424
Professor Sandvig
Outline
Sql Server Management Studio
 Security
 Programmability
 Example

Caveat
Sql Server very
complex &
feature-rich
product
 Many features not
discussed
 Focus on
“developer”
features

1704 pages
Database Admin
SQL Server Management Studio
Provides interface to Sql
Server
 More features than
Visual Studio

◦ Security
 Users & permissions
◦ Programmability
◦ Diagrams
 Relationships
◦ Optimization
SQL Server Management Studio

Features:Visual
Studio vs. SQMS
Security
Authentication & Authorization
 Security is two step process:

1. Authentication:
 Identifying user
 Username & password
2. Authorization:
 Check permissions for specific actions:
 Select, insert, update, delete…
Authentication

Sql Server supports:
1. Windows Authentication
2. Sql Server Authentication
Windows Authentication

User identified by Windows OS
◦ Windows prompt
Advantage: use existing
Windows login
 Many
organizations use
Active Directory

Sql Server Authentication
User created on
SQL Server
 Sql Server manages
username/password
 Benefit:

◦ Can create specialized users with limited
permissions
◦ Example: ZipCodeReader
Authorization
Every request must be authorized
 Common methods:

◦ Create limited user
 ZipCodeReader
◦ Anonymous user:




Web site
Network Service
Account
Built-in Windows
acct
SQL Server Authorization

SQL Server
allows granular
control:
◦ Tables
◦ Fields
◦ Stored
procedures
Programmability
Store queries on
database
 Reuse in many
applications
 Division of duties

◦ Devs & DBAs
Security options
 Hide underlying tables

Programmability

Stored procedures
◦
◦
◦
◦
◦
Store query on database
Accept arguments
Assign permissions to procedure
T-Sql supports complex queries
CRUD support
Programmability

Functions
◦ Read only
◦ Support input parameters
◦ Utilized inside sql statements:
Select *
FROM funGetNames
Where NameF LIKE ‘s%’
Order by NameL
◦ May use in other functions, SPs, SQL queries
Example:

Sql Server Management Studio:
◦
◦
◦
◦
Create database
Add table
Create user
Add user permissions to database