Download - Sql Server Advanced Features
![Page 1: Sql Server Advanced Features](https://reader030.vdocuments.us/reader030/viewer/2022033102/568160c7550346895dcff6ae/html5/thumbnails/1.jpg)
Sql Server Advanced Sql Server Advanced FeaturesFeaturesMIS 424Professor Sandvig
![Page 2: Sql Server Advanced Features](https://reader030.vdocuments.us/reader030/viewer/2022033102/568160c7550346895dcff6ae/html5/thumbnails/2.jpg)
OutlineOutlineSql Server Management StudioSecurityProgrammabilityExample
![Page 3: Sql Server Advanced Features](https://reader030.vdocuments.us/reader030/viewer/2022033102/568160c7550346895dcff6ae/html5/thumbnails/3.jpg)
CaveatCaveatSql Server
very complex & feature-rich product
Many features not discussed
Focus on “developer” features 1704 pages
![Page 4: Sql Server Advanced Features](https://reader030.vdocuments.us/reader030/viewer/2022033102/568160c7550346895dcff6ae/html5/thumbnails/4.jpg)
Database AdminDatabase Admin
![Page 5: Sql Server Advanced Features](https://reader030.vdocuments.us/reader030/viewer/2022033102/568160c7550346895dcff6ae/html5/thumbnails/5.jpg)
SQL Server Management SQL Server Management StudioStudioProvides interface to
Sql ServerMore features than
Visual Studio◦Security
Users & permissions◦Programmability◦Diagrams
Relationships◦Optimization
![Page 6: Sql Server Advanced Features](https://reader030.vdocuments.us/reader030/viewer/2022033102/568160c7550346895dcff6ae/html5/thumbnails/6.jpg)
SQL Server Management SQL Server Management StudioStudio
Features: Visual Studio vs. SQMS
![Page 7: Sql Server Advanced Features](https://reader030.vdocuments.us/reader030/viewer/2022033102/568160c7550346895dcff6ae/html5/thumbnails/7.jpg)
SecuritySecurityAuthentication & AuthorizationSecurity is two step process:1. Authentication:
Identifying user Username & password
2. Authorization: Check permissions for specific actions:
Select, insert, update, delete…
![Page 8: Sql Server Advanced Features](https://reader030.vdocuments.us/reader030/viewer/2022033102/568160c7550346895dcff6ae/html5/thumbnails/8.jpg)
AuthenticationAuthenticationSql Server supports:1. Windows Authentication2. Sql Server Authentication
![Page 9: Sql Server Advanced Features](https://reader030.vdocuments.us/reader030/viewer/2022033102/568160c7550346895dcff6ae/html5/thumbnails/9.jpg)
Windows AuthenticationWindows AuthenticationUser identified by Windows OS
◦Windows promptAdvantage: use existing
Windows loginMany
organizations use Active Directory
![Page 10: Sql Server Advanced Features](https://reader030.vdocuments.us/reader030/viewer/2022033102/568160c7550346895dcff6ae/html5/thumbnails/10.jpg)
Sql Server AuthenticationSql Server AuthenticationUser created on
SQL ServerSql Server manages
username/passwordBenefit:
◦Can create specialized users with limited permissions
◦Example: ZipCodeReader
![Page 11: Sql Server Advanced Features](https://reader030.vdocuments.us/reader030/viewer/2022033102/568160c7550346895dcff6ae/html5/thumbnails/11.jpg)
AuthorizationAuthorizationEvery request must be authorizedCommon methods:
◦Create limited user ZipCodeReader
◦Anonymous user: Web site Network Service Account Built-in Windows
acct
![Page 12: Sql Server Advanced Features](https://reader030.vdocuments.us/reader030/viewer/2022033102/568160c7550346895dcff6ae/html5/thumbnails/12.jpg)
SQL Server AuthorizationSQL Server AuthorizationSQL Server
allows granular control:◦Tables◦Fields◦Stored
procedures
![Page 13: Sql Server Advanced Features](https://reader030.vdocuments.us/reader030/viewer/2022033102/568160c7550346895dcff6ae/html5/thumbnails/13.jpg)
ProgrammabilityProgrammabilityStore queries on
databaseReuse in many
applicationsDivision of duties
◦Devs & DBAsSecurity optionsHide underlying
tables
![Page 14: Sql Server Advanced Features](https://reader030.vdocuments.us/reader030/viewer/2022033102/568160c7550346895dcff6ae/html5/thumbnails/14.jpg)
ProgrammabilityProgrammabilityStored procedures
◦Store query on database◦Accept arguments◦Assign permissions to procedure◦T-Sql supports complex queries◦CRUD support
![Page 15: Sql Server Advanced Features](https://reader030.vdocuments.us/reader030/viewer/2022033102/568160c7550346895dcff6ae/html5/thumbnails/15.jpg)
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
![Page 16: Sql Server Advanced Features](https://reader030.vdocuments.us/reader030/viewer/2022033102/568160c7550346895dcff6ae/html5/thumbnails/16.jpg)
Example:Example:Sql Server Management Studio:
◦Create database◦Add table◦Create user◦Add user permissions to database