Chapter 10, Managing Access to SQL Server 2000
Chapter 10, Managing Access to SQL Server 2000
|1|Chapter Overview
A.Understanding the Authentication Process
B.Understanding the Authorization Process
C.Creating and Managing Logins
Chapter 10, Lesson 1
Understanding the Authentication Process
|2|1.Authentication Process
A.Windows authentication
1.The user presents either a Kerberos ticket or an access token.
2.SQL Server verifies the authenticity of the Kerberos ticket or access token.
3.SQL Server checks the user’s name against the list of Microsoft Windows users and groups permitted access and then grants or denies access.
4.This type of connection is called a trusted connection.
B.SQL Server authentication
1.The user submits a login name and password.
2.The name and password are submitted in clear text unless SSL encryption is used for the entire session.
3.SQL Server checks the user’s name against the list of SQL Server security accounts permitted access and then grants or denies access.
4.You must use SQL Server authentication to connect to SQL Server running on Microsoft Windows Me or Windows 98.
|3|2.Authentication Method Security Issues
A.Authentication
1.In Windows authentication, the Windows operating system authenticates the user by using an encrypted name and password.
2.In SQL Server authentication, the user is never authenticated by the Windows operating system.
B.Authentication credentials
1.In Windows authentication, a Kerberos ticket or access token is presented to SQL Server.
2.In SQL Server authentication, the user name and password are submitted in clear text unless SSL encryption is used for the entire session.
C.Password policies
1.The Windows operating system supports password policies.
2.No password policies are supported in SQL Server.
D.Account lockout policies
1.The Windows operating system supports account lockout policies.
2.No account lockout policies are supported in SQL Server.
|4|3.Net-Library Issues
A.The client and Microsoft SQL Server must support a matching set of Net-Libraries.
B.Default client Net-Libraries are TCP/IP Sockets and Named Pipes, and connections are attempted in that order.
C.Use the Client Network Utility to modify the configured client Net-Libraries, to modify their connection order, or to enable SSL encryption.
D.The Named Pipes and Multiprotocol Net-Libraries require an authenticated connection to the Windows domain. This is achieved either by using a login to the domain or by connecting to a share within the Microsoft Windows domain.
E.TCP/IP Sockets and the remaining Net-Libraries do not require any authentication to attempt to connect to SQL Server.
|5|4.SQL Server Authentication Modes
A.Windows Authentication Mode
1.Default mode
2.Only permits connections using Windows authentication
3.More secure than Mixed Mode
B.Mixed Mode
1.You must enable Mixed Mode, either during setup or in the properties of SQL Server in SQL Server Enterprise Manager.
2.Switching authentication modes requires a restart of the SQL Server service.
3.Permits connections using either Windows authentication or SQL Server authentication
4.Required for users, such as NetWare or UNIX clients, connecting to SQL Server without authentication by the Windows operating system
|6|5.Security Account Delegation
A.SQL Server can impersonate an authenticated user to access resources outside of SQL Server, such as in the file system on a remote system or on another instance of SQL Server.
B.All participating servers must be running Windows 2000 with Active Directory directory services installed.
C.Security account delegation must be enabled in Active Directory on each participating server.
D.The SQL Server service domain user account must be trusted for delegation.
E.The user requesting delegation must be trusted for delegation.
F.The SQL Server instance must have a Service Principal Name (SPN).
Chapter 10, Lesson 2
Understanding the Authorization Process
|7|1.Server Roles Grant Server-Wide Permissions
A.The sysadmin role has permission to perform any activity within SQL Server.
B.The serveradmin role has permission to configure server-wide settings and shut down SQL Server.
C.The setupadmin role has permission to install and configure linked servers, remote servers, and replication. Mark stored procedure for execution at startup.
D.The securityadmin role has permission to manage server logins, CREATE DATABASE statement permissions, read error logs, and change passwords.
E.The processadmin role has permission to manage or kill SQL Server processes.
F.The dbcreator role has permission to create, alter, and delete databases.
G.The diskadmin role has permission to manage disk files and backup devices.
H.The bulkadmin role has permission to execute the BULK INSERT statement.
|8|2.Database-Specific Permissions
A.Database owner—A specified user who can perform any activity within the database.
B.DBO role—Members of the serveradmins server role map to the DBO role in each database and can perform any activity within the database.
C.User—A login that is permitted database access.
D.Guest user—A special account that permits authenticated logins to have limited database access.
E.Public role—All permitted users in a database belong to the public role in that database.
F.Fixed database role—Roles in each database with predefined permissions.
G.User-defined database role—Custom roles in each database with definable permissions.
H.Statement permissions—Permissions to execute specified statements, such as CREATE PROCEDURE.
I.Object permissions—Permissions to access specified database objects, such as tables or views.
J.Application role—Custom roles in each database granted to an application for all users accessing SQL Server through that application.
|9|3.Fixed Database Roles
A.The db_owner role has permission to perform any task within a database.
B.The db_accessadmin role has permission to add or remove Windows groups and users, and SQL Server security accounts as database users.
C.The db_securityadmin role has permission to manage permissions, roles, role memberships, and object ownership in a database.
D.The db_ddladmin role has permission to add, modify, or drop database objects in a database.
E.The db_backupoperator has permission to run DBCC commands, issue checkpoints, and back up the database.
F.The db_datareader role has permission to read data from any table or view in the database.
G.The db_datawriter role has permission to modify or delete data from any table or view in the database.
H.The db_denydatawriter role is denied permission to read data from any table or view in the database.
I.The db_denydatareader is denied permission to modify or delete data from any table or view in the database.
Chapter 10, Lesson 3
Creating and Managing Logins
|10|1.Login Accounts Using Create Login Wizard
A.Specify the authentication type for the login.
1.Link an existing Windows group or user to the login.
2.Create a security account within SQL Server.
B.Grant or deny SQL Server access to the login account.
C.Define a server role, if any.
D.Specify the databases to which the login will be a permitted user.
E.Can create only one account at a time
|11|2.Login Accounts Using SQL Server Enterprise Manager Directly
A.Define the authentication type, server role(s), and database access.
B.Can also configure a default language and database for the login
C.Can also view existing logins that are currently members of a server role or database role during the login creation process
D.Can also add login to fixed database role within any database
E.Can create only one account at a time
|12|3.User-Defined Database Roles Using SQL Server Enterprise Manager
A.Create the user-defined database role within a specific database.
B.Add users within the database to that role.
C.A user-defined database role has no permissions assigned to it automatically; it is simply a collection of users within a database.
|13|4.Windows Login Accounts Using Transact-SQL
A.sp_grantlogin 'login'—Creates a login for a Windows group or user.
B.sp_revokelogin 'login'—Revokes a login for a Windows group or user.
C.sp_denylogin 'login'—Denies login access to a Windows group or user.
D.sp_defaultdb 'login', 'database'—Changes the default database for either type of login.
E.sp_defaultlanguage 'login', 'language'—Changes the default language for either type of login.
|14|5.SQL Server Login Accounts Using Transact-SQL
A.sp_addlogin 'login', ['password’, 'database', 'language’, 'sid’, 'encryption_option']—Creates a new security account within SQL Server, optionally specifying additional parameters.
B.sp_droplogin 'login'—Drops a SQL Server login.
C.sp_password 'old_password', 'new_password', 'login'—Adds or changes a password for a SQL Server login.
D.sp_defaultdb 'login', 'database'—Changes the default database for either type of login.
E.sp_defaultlanguage 'login', 'language'—Changes the default language for either type of login.
|15|6.Server Roles and Database Access Using Transact-SQL
A.sp_addsrvrolemember 'login', 'role'—Adds a login as a member of a server role.
B.sp_dropsrvrolemember 'login', 'role'—Drops a login from membership in a server role.
C.sp_grantdbaccess 'login', 'name_in_db'—Grants a login database access, generally using the same name as the login name.
D.sp_revokedbaccess 'name'—Removes a login from the list of permitted users of a database.
|16|7.Database Roles Using Transact-SQL
A.sp_changedbowner 'login', 'remap_alias_flag'—Changes the owner of the database.
B.sp_addrolemember 'role', 'security_account'—Adds a security account to a database role.
C.sp_droprolemember 'role', 'security_account'—Drops a security account from a database role.
D.sp_addrole 'role’, 'owner'—Adds a user-defined database role.
E.sp_droprole 'role'—Removes a user-defined database role.
|17|8.Viewing Access Information Using SQL Server Enterprise Manager
A.View on a user-by-user basis in the Logins container within the Security container.
B.View server role membership in the Server Roles container within the Security container.
C.View database access on a per-user basis in the Users container in each database.
D.View database role membership in the Roles container in each database.
|18|9.Viewing Access Information Using Transact-SQL
A.sp_helplogins ['login']—All logins or a specific login, including database access rights and role memberships.
B.sp_helpsrvrolemember ['role']—All server roles and their members, or a specified server role and its membership.
C.sp_helpuser ['security_account']—All users in the current database, or a specified user in the database, and their database role membership.
D.sp_helprolemember ['role']—All database roles and their members, or a specified database role and its membership.
E.sp_helpntgroup ['name']—All Windows groups with SQL Server access to a database, or the access rights of a specified Windows group.
|19|Chapter Summary
A.Authentication is required for access to SQL Server, and authorization is required to perform actions within SQL Server.
B.SQL Server supports Windows authentication and SQL Server authentication.
C.SQL Server can run in Windows Authentication Mode or Mixed Mode.
D.Server roles are used to grant server-wide administration rights.
E.Database roles are used to grant database-specific rights.
F.Create login accounts one by one in SQL Server Enterprise Manager, and create many at once using Transact-SQL.
G.View access information on a user basis or a role basis using SQL Server Enterprise Manager or Transact-SQL.
Outline, Chapter 101
Microsoft SQL Server 2000 System Administration