sql server advanced features
DESCRIPTION
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. Database Admin. - PowerPoint PPT PresentationTRANSCRIPT
Sql Server Advanced Sql Server Advanced FeaturesFeaturesMIS 424Professor Sandvig
OutlineOutlineSql Server Management StudioSecurityProgrammabilityExample
CaveatCaveatSql Server
very complex & feature-rich product
Many features not discussed
Focus on “developer” features 1704 pages
Database AdminDatabase Admin
SQL Server Management SQL Server Management StudioStudioProvides interface to
Sql ServerMore features than
Visual Studio◦Security
Users & permissions◦Programmability◦Diagrams
Relationships◦Optimization
SQL Server Management SQL Server Management StudioStudio
Features: Visual Studio vs. SQMS
SecuritySecurityAuthentication & AuthorizationSecurity is two step process:1. Authentication:
Identifying user Username & password
2. Authorization: Check permissions for specific actions:
Select, insert, update, delete…
AuthenticationAuthenticationSql Server supports:1. Windows Authentication2. Sql Server Authentication
Windows AuthenticationWindows AuthenticationUser identified by Windows OS
◦Windows promptAdvantage: use existing
Windows loginMany
organizations use Active Directory
Sql Server AuthenticationSql Server AuthenticationUser created on
SQL ServerSql Server manages
username/passwordBenefit:
◦Can create specialized users with limited permissions
◦Example: ZipCodeReader
AuthorizationAuthorizationEvery request must be authorizedCommon methods:
◦Create limited user ZipCodeReader
◦Anonymous user: Web site Network Service Account Built-in Windows
acct
SQL Server AuthorizationSQL Server AuthorizationSQL Server
allows granular control:◦Tables◦Fields◦Stored
procedures
ProgrammabilityProgrammabilityStore queries on
databaseReuse in many
applicationsDivision of duties
◦Devs & DBAsSecurity optionsHide underlying
tables
ProgrammabilityProgrammabilityStored procedures
◦Store query on database◦Accept arguments◦Assign permissions to procedure◦T-Sql supports complex queries◦CRUD support
ProgrammabilityProgrammabilityFunctions
◦Read only◦Support input parameters◦Utilized inside sql statements:
Select * FROM funGetNamesWhere NameF LIKE ‘s%’Order by NameL
◦May use in other functions, SPs, SQL queries
Example:Example:Sql Server Management Studio:
◦Create database◦Add table◦Create user◦Add user permissions to database