Page 90 - DCAP508_DATABASE_ADMINISTRATION

Database Administration Sartaj Singh, Lovely Professional University

Notes Unit 6: SQL Server Authentication

CONTENTS
Objectives
Introduction
6.1 Authentication Modes
6.1.1 Windows Authentication
6.1.2 SQL Server Authentication
6.2 Permissions

6.2.1 Permissions Applicable to Specific Securables
6.2.2 Permissions Validation
6.3 Disadvantages of SQL Server Authentication
6.4 Advantages of SQL Server Authentication
6.5 SQL Server Encryption
6.5.1 Encryption Hierarchy
6.5.2 Encryption Mechanisms
6.6 Summary
6.7 Keywords
6.8 Review Questions
6.9 Further Readings

Objectives

After studying this unit, you will be able to:
Understand the basic principles of authentication
Learn about various permissions in SQL server authentication
Analyze the process of SQL server encryption

Introduction

A user passes through two stages of security when working in Microsoft® SQL Server™:
authentication and authorization (permissions validation). The authentication stage identifies
the user using a login account and verifies only the ability to connect to an instance of SQL
Server. If authentication is successful, the user connects to an instance of SQL Server. The user
then needs permissions to access databases on the server, which is done by granting access to an
account in each database, mapped to the user login. The permissions validation stage controls
the activities the user is allowed to perform in the SQL Server database.

6.1 Authentication Modes

Microsoft SQL Server can operate in one of two security (authentication) modes:
1. Windows Authentication Mode (Windows Authentication): Windows Authentication
mode allows a user to connect through a Microsoft Windows NT® 4.0 or Windows® 2000
user account.

84 LOVELY PROFESSIONAL UNIVERSITY