Da Notes - 70-028 - S

Personal notes on the MCDBA tests. These notes are from the various resources I used to pass the MCDBA exams including the MCDBA sample exams from Transcender.

Security

Windows NT Authentication mode (SQL 6.5 : Integrated Mode). Requires use of Named Pipes or Multiprotocol. User must be authenticated by NT before connection to SQL is allowed. Sometimes referred to as "trusted connection". User does not have to provide separate credentials to access SQL. You can explicitly deny access to SQL server to an NT account. To deny access to a SQL Server account, remove login or don't create it.

Mixed mode Authentication - Advantages: Internet clients, Unix hosts or Netware clients, that cannot authenticate to NT can use it to connect. 

Security - Database Configuring and Managing :

Access to SQL granted by means of login entry in syslogins table in Master database.

User must have either a mapping that associates an NT account (group or users) to an entry in syslogins in the case of NT authentication mode or have a separate login entry in the case of SQL Server authentication.

Database permissions are separate from SQL server logins. Access to SQL through entries in syslogins does not give access to databases. 

Default Login :  sa (no password as default) and BUILTIN\Administrator = Superuser for SQL Server 

Database Access:
In order to gain access to a database, you must have a username in database mapped to a SQL Server login or a 'guest' account must exist in database. 
Database username and login name do not have to match, although they should for ease of administration.

Many SQL 6.5 commands, such as "sp_adduser" are still supported. 

Guest User
The guest user account allows a login without a user account to access a database.

A login assumes the identity of the guest user when all of the following conditions are met: 

The login has access to SQL Server, but does not have access to the database with their own user account. 

The database contains a guest user account. 

Permissions can be applied to the guest user as if it were any other user account.

The guest user can be deleted and added to all databases except master and tempdb, where it must always exist.

By default, a guest user account does not exist in newly created databases. However, if guest is added to the model database, every subsequently created database will have this account.

Roles : 
Analogous to NT Groups, except a member of any role can add other users to same role.

Roles replace the use of SQL 6.x groups.

Unlike SQL groups, users can be members of  multiple roles and roles can be nested.

Aliases, which are used to impersonate a user in a database, are still supported.

There are 4 types of roles: 
· Fixed Server Roles 
· Fixed Database Roles 
· User-defined Database Roles 
· Application Roles 

Fixed Server Roles:
Dbcreator Create and alter databases
Diskadmin Manage disk files
Processadmin Manage SQL Server processes
Securityadmin Manage and audit server logins
Serveradmin Configure server-wide settings
Setupadmin Install replication. Set up linked servers.
sysadmin A L L 

Fixed Database Roles:
public:  Maintain all default permissions. Every DB has a public role. All users are members. Can't be dropped.

db_owner A L L

db_accessadmin Add, remove database users, groups and roles

db_ddladmin Add, modify, or drop database objects. Run DDL commands, except those that modify permissions.

db_security admin Assign statement and object permissions

db_backupoperator Backup database

db_datareader Read data from any table

db_writer Add, change , or delete data from tables

db_denydatareader Cannot read data from any table

db_denydatawriter Cannot change data from any table

User-defined Database Role 
When a group of people needs to perform the same activities in SQL Server

Application Roles 
Used to restrict access to database through an application. Scope of role is the database. Application roles have no members.  Activated by lauching an application using sp_setapprole stored procedure. Password required for activation application role. Users lose all permissions in database, except those of the application role and those given to public. Has no effect on user permissions in other databases.  Role is only deactivated for user only when the user disconnects from SQL. 


Permissions
Permissions in databases are cumulative, except where a permission has been explicitly denied (analogous to no access NTFS permission). Implicit user permissions, such as those that are acquired through role membership or those that are implicitly given to Database Object Owners, can not be directly viewed. Database Object Owners have all permissions on objects they create and can grant, revoke or deny permissions to all users, including the Database Owner, on these objects. 

Types of permission 
Permissions apply to statements and objects.

Statement permissions give users the ability to execute Transact-SQL commands, such as CREATE DATABASE. Statement Permissions: create database, create table, create view, create rule, create default, backup database, backup log 

Object permissions give users the ability to do something, such as viewing or updating information in a table or executing a stored procedure. 
Object Permissions: Select, insert, update, delete, references, execute. Select, insert, update, delete and references can be applied to tables and views, select, update, references to columns, and execute to stored procedures. 

Predefined (implicit) permissions apply to fixed roles or object owners. 

Assigning Permissions: 
Grant - Can perform action 
USE <database>GRANT {ALL | statement[,...n]}ON <table>TO security_account[,...n]

Deny - Cannot perform action and cannot override 
USE <database>DENY{ALL | statement[,...n]}ON <table>TO security_account[,...n]

Revoke - Neutral. Cannot perform action but can be overridden by role membership 
USE <database>REVOKE {ALL | statement[,...n]}ON <table>FROM security_account[,...n]


Permissions can be granted to views without having to grant permissions to the underlying tables that comprise the views, provided the ownership chain is not broken. 

Users who have Execute permissions on stored  procedures do not need to be granted permissions to modify or view the data that the stored procedure needs access to.

Ownership Chains:
Objects, such as views, have owners. When a single owner creates a series of objects there is a single ownership chain. (i.e. When the dbo creates View1 and then creates View2 that is based on View1, there is a single chain. If the dbo, however, grants the permission to create a view to another user and that user creates a third view based on View2, the ownership chain is broken: the user does not own the object that his or her view depends on. SQL server will check permissions only once if there is a single ownership chain--on the view itself and not on the objects it may depend on.) 

However, if there is a broken ownership chain, SQL will check permissions on all the objects in the chain where there is a change in ownership. So, if Mary grants Joe the select permission on View3 and Mary does not own the objects that View3 depends on, Joe's permissions will be checked on those objects. If Joe does not have permissions on the upper objects in the chain, his query will fail.

Use sp_changeobjectowner to change ownership of objects in database.

Recommendations: 
Use Mixed Mode for non-trusted or Internet Clients

Use sysadmin role rather the sa account 

Remove NT accounts first, then SQL Server accounts 
dbo user should own all objects to prevent broken ownership chains 

Use stored procedures and views to simplify security 

Services

MSSQLServer Service - Database server for SQL Server.

SQLServerAgent service - Agent that runs scheduled administrative tasks.

Microsoft Search service (Win NT only) - Full-text search engine.

MSDTC service (Win NT only) - Manager for distributed transactions.

Software

Data Transformation Services (DTS) - provides a set of COM objects so you can execute complex data conversions between OLE DB data providers. You can use Visual Basic Scripting Edition or Microsoft JScript to create DTS scripts.

English Query - Pose questions in English that generate SQL statements.

Enterprise manager (EM or SEM) - Administrative client that can be a snap-in to MMC. Admin tools and wizards Client configuration.

Help and Books online -  Application help via help menu or button

Isql - A command prompt utility that uses the DB-Library interface.

MS DTC - for distributing transactions among multiple SQL Servers using a two-phase commit protocol. 

OLAP services - provides OLAP processing capabilities against heterogeneous OLE DB data sources.

Osql - A command prompt utility that uses ODBC. Bcp - batch utility used to import and export data

Performance monitor - a tool for monitoring resource usage on a computer running Microsoft Windows NT.

SQL Distributed Management Objects (SQL-DMO) -  redesigned and expanded to reflect SQL Server 7.0 features and architecture but very similar to SQL Server 6.x.

SQL Server Profiler - captures a continuous picture of server activity in real time.

SQL Server Query Analyzer (ISQL/w in SQL 6.5) - color-coded editor, Graphical Showplan, and Help integration for SQL checking syntax.  Uses ODBC.

Web Assistant Wizard - Export SQL Server data to an HTML file. Import tabular data from an HTML file  and post to and read from HTTP and FTP locations.

SQL Server Agent

Alerts - Actions to take when specific events occur, such as a specific error message, error severity level, or a database reaching a defined limit. The alert can be defined to take such actions as sending an e-mail, paging an operator, or running a job to address the problem.

Jobs - Consists of a one or more steps to be run. The steps are T-SQL code. Jobs can be scheduled to execute at specific times, on demand or recurring intervals.

Operators - Users identified by their network account or e-mail ID who can fix server problems. Alerts are sent to them through e-mail, a pager, or a net send network command.