1 / 17

Sql Server Advanced Features

Explore advanced features of SQL Server Management Studio for enterprise databases, focusing on security, scalability, reliability, and programmability. Learn about referential integrity, user permissions, and creating specialized users for enhanced database management.

stevenbernstein
Download Presentation

Sql Server Advanced Features

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Sql Server Advanced Features MIS 424 Professor Sandvig

  2. Outline • Why use an enterprise database? • Sql Server Management Studio • Referential Integrity • Security • Programmability • Example

  3. Why Use Enterprise database • Scalability • Security • Reliability • Programmability • Auditing • Other…

  4. Caveat • Enterprise databases complex & feature-rich products • Many features not discussed • Focus on “developer” features

  5. SQL Server Management Studio • Provides interface to Sql Server • More features than Visual Studio • Security • Users & permissions • Programmability • Diagrams • Relationships • SSMS free download

  6. SQL Server Management Studio • Features: Visual Studio vs. SQMS

  7. Referential Integrity • Maintain integrity of relationships between primary and foreign keys • If primary key deleted foreign keys also deleted • SSMS tools • Database Diagrams

  8. Referential Integrity

  9. Security • Authentication & Authorization • Security is two step process: • Authentication: • Identifying user • Username & password • Authorization: • Check permissions for specific actions: • Select, insert, update, delete…

  10. Authentication • Sql Server supports: • Windows Authentication • Sql Server Authentication

  11. Windows Authentication • User identified by Windows OS • Windows prompt • Advantage: use existingWindows login • Manyorganizations use Active Directory

  12. Sql Server Authentication • User created on SQL Server • Sql Server manages username/password • Benefit: • Can create specialized users with limited permissions • Example: ZipCodeReader

  13. Authorization • Every request must be authorized • Common methods: • Create limited user • MusicStoreReader • Anonymous user: • IIS Network Service

  14. Authorization • Authorization in 324 & 424 • Visual Studio – • Windows authentication • Database administrator • Full privileges • Upload to server • Runs under IIS • Network Service • CRUD (no create table, drop table, etc.)

  15. SQL Server Authorization • SQL Server allows granular control: • Tables • Fields • Stored procedures

  16. Programmability • Store queries on database • Stored procedures • Hide underlying tables • Control permissions • T-SQL – complex queries

  17. Example: • Sql Server Management Studio: • ER Diagram • Referential Integrity • Create database • Add table • Create user • Add user permissions to database

More Related