Page 93 - DCAP508_DATABASE_ADMINISTRATION

Unit 6: SQL Server Authentication

Notes

Notes When connecting to an instance of SQL Server running on Windows NT 4.0 or
Windows 2000 using Named Pipes, the user must have permission to connect to the
Windows NT Named Pipes IPC, \\<computername>\IPC$. If the user does not have
permission to connect, it is not possible to connect to an instance of SQL Server using
Named Pipes unless either the Windows NT 4.0 or Windows 2000 guest account on the
computer is enabled (disabled by default), or the permission “access this computer from
the network” is granted to their user account.

Self Assessment

Fill in the blanks:
1. A user passes through ………………… stages of security when working in Microsoft® SQL
Server.

2. The ………………… stage controls the activities the user is allowed to perform in the SQL
Server database.
3. ………………… mode allows a user to connect through a Microsoft Windows NT® 4.0 or
Windows® 2000 user account.
4. ………………… allows users to connect to an instance of SQL Server using either Windows
Authentication or SQL Server Authentication.
5. SQL Server Authentication is provided for ………………… compatibility.
6. A user’s network security attributes are established at ………………… time and are
validated by a Windows domain controller.
7. ………………… integration operates over any supported network protocol in SQL Server.

6.2 Permissions

Every SQL Server securable has associated permissions that can be granted to a principal.
The following describes the general conventions that are followed for naming permissions:

CONTROL: Confers ownership-like capabilities on the grantee. The grantee effectively
has all defined permissions on the securable. A principal that has been granted CONTROL
can also grant permissions on the securable. Because the SQL Server security model is
hierarchical, CONTROL at a particular scope implicitly includes CONTROL on all the
securables under that scope. For example, CONTROL on a database implies all permissions
on the database, all permissions on all assemblies in the database, all permissions on all
schemas in the database, and all permissions on objects within all schemas within the
database.

ALTER: Confers the ability to change the properties, except ownership, of a particular
securable. When granted on a scope, ALTER also bestows the ability to alter, create, or
drop any securable that is contained within that scope. For example, ALTER permission
on a schema includes the ability to create, alter, and drop objects from the schema.
ALTER ANY <Server Securable>, where Server Securable can be any server securable.
Confers the ability to create, alter, or drop individual instances of the Server Securable.
For example, ALTER ANY LOGIN confers the ability to create, alter, or drop any
login in the instance.

LOVELY PROFESSIONAL UNIVERSITY 87