Lesson 5 - Administering a Database

3 Options for SQL Server Service Account

(1) Local System account; (2) Local User account; (3) Domain User account

Order of Authentication

(1) Server; (2) Databases; (3) Objects within Databases.

3 Methods of User Identification

(1) Windows User; (2) Windows User Group; (3) SQL Server Authentication.

3 Ways to Login to SQL Server

(1) Windows domain login; (2) username login; (3) SQL Server login.

3 Kinds of Server Roles

(1) fixed roles; (2) public role; (3) user-defined roles

Domain User Account

This is the recommended login account because the SQL Server can then use the Windows account specifically created for it; you can then grant administrator rights to the SQL Server account.

Grant

This permission means that a user can access the object.

Deny

This permission overrides a granted permission.

Revoke

This permission removes the permission that has been assigned, regardless of whether it was a denied permission or a granted permission.

db_owner

This special fixed database role has all permissions in the database; includes all the capabilities of the other roles and differs from the dbo user role; this is not the database-level equivalent of the server sysadmin role because an OBJECT-LEVEL DENY WILL OVERRIDE MEMBERSHIP IN ANY ROLE.

BUILTINS/Administrator User

This system user is automatically created during installation of the SQL Server instance and includes all Windows users in the Windows Administration group; allows a choice of what groups or users are added during setup; can be deleted or modified as desired after installation.

Database Username

When a login is granted access to a database, that login is also assigned a _______________________ , which may be the same as the login name --OR-- may be some other name by which the login will be known within the database.

CREATE LOGIN 'COL\lfischer'

Script command for creating a SQL Server login. (uses Windows user info from Active Directory)

True

TRUE OR FALSE - The TSQL script command used to grant access to users must be issued from within the database to which the user is to be granted access.

False

TRUE OR FALSE - Users may NOT be assigned to multiple roles, so that multiple security paths from a user to an object are not created.

True

TRUE OR FALSE - Users must be explicitly granted access to any given database.

Windows User Groups

Any Windows user who is a member of this group can be authenticated by SQL Server; access, roles, and permissions can be assigned to this group, and they will apply to any user in the group.

Guest User

A database user account which is available to SERVER ONLY USERS who wish to access a database but who have not been declared a user within the database.

Application Role

A hard-coded database-specific role intended to allow an application to gain access to the database regardless of the application's user.

Database Access

A SQL Server user can be granted access to databases in the _________________ tab.

SQL Server Service Account

A Windows account designated specifically for the SQL Server to use in order to access files and directories.

Mixed Mode

Authentication setting which allows you to connect to a SQL server using Windows authentication --OR-- SQL Server authentication.

User

BUILTINS/Administrator is a (USER, ACCOUNT or ROLE?) that SQL Server automatically creates during installation of the software

sa Account

Built-in SQL administrator account associated with SQL Server authentication (mixed mode).

Standard Role Name Parameters

Can be up to 128 characters and cannot include a backslash, be null, or be an empty string.

User-defined Roles

Custom roles that serve as groups; if you never assign permissions to database objects, then role members will never be able to access databases or the information inside of them.

db_securityadmin

Fixed database role permits a user to manage database-level security—including roles and permissions.

Public

Fixed server role that CAN HAVE OBJECT PERMISSIONS like a standard role; every user is automatically a member of the public role and cannot be removed, so this role serves as a baseline or minimum permission level.

Setupadmin

Fixed server role that can configure linked servers, extended stored procedures, and the startup stored procedure.

Serveradmin

Fixed server role that can configure the server-wide settings, including setting up full-text searches and shutting down the server.

Diskadmin

Fixed server role that can create, alter, and drop disk files.

Dbcreator

Fixed server role that can create, alter, drop, and restore databases.

Processadmin

Fixed server role that can kill a running SQL Server process.

Securityadmin

Fixed server role that can manage the logins for the server.

Sysadmin

Fixed server role that can perform any activity in the SQL Server installation, regardless of any other permission setting; this role even overrides denied permissions on an object.

Bulkadmin

Fixed server role that can perform bulk insert operations.

Local System Account

If you are using a single-server installation, you may wish to choose this account, because the SQL Server can use the local system account of the operating system for permission to the machine; drawback of using this account login is that it fails to provide the necessary network security credentials for databases because it has privileges inside the operating system that the administrator's account does not -- creates a potential security hole.

Local User Account

If you find that access to the network is not actually required, this is the perfect option to consider because a local user account cannot be used outside the server environment.

Rows

Insert and delete object permissions affect entire __________.

WITH GRANT

Object permission type option which provides the grantee the ability to grant permission for an object.

DRI (References)

Object permission which grants the right to create foreign keys with Declarative Referential Integrity (DRI).

Delete

Object permission which grants the right to delete existing data.

Execute

Object permission which grants the right to execute stored procedures or user-defined functions.

Insert

Object permission which grants the right to insert data.

Update

Object permission which grants the right to modify existing data; if a WHERE clause is used in conjunction, the SELECT right is required as well; can be set on SPECIFIC COLUMNS.

Select

Object permission which grants the right to select data; this permission can be applied to SPECIFIC COLUMNS.

GRANT, REVOKE, DENY

Object permissions are assigned with the SQL DCL commands __________ , __________ , and __________ .

Column

Only SELECT and UPDATE object permissions can be set at the ___________ level.

Hierarchical Role Structure

Perfect for complex security structures; a series of nested user-defined database roles; a change in the lower level affects all upper levels.

Object Permissions

Permissions that allow a user to act on database objects, such as tables, stored procedures, and views.

sysadmin

Server-level role wherein user has full access to every server function, database, and object for that server; the ultimate security role, which has full access to all databases.

EXEC sp_addsrvrolemember 'COL\lfischer', 'diskadmin'

System stored procedure script command which is EXECUTED when assigning a user to server roles.

EXEC sp_addrolemember 'Manager', 'Joe'

System stored procedure script command which is EXECUTED when you want to assign a DATABASE USERNAME to a 'Manager' ROLE.

EXEC sp_adduser 'Guest'

System stored procedure script command which is EXECUTED when you want to create the Guest account.

EXEC sp_dropRoleMember 'Manager', 'Joe'

System stored procedure script command which is EXECUTED when you want to remove a DATABASE USERNAME from a 'Manager' ROLE.

True

TRUE OR FALSE - A user may belong to multiple server roles.

False

TRUE OR FALSE - A user may belong to only one fixed database roles.

True

TRUE OR FALSE - A user may have multiple permission paths to an object (e.g., individually, through a standard database role, and through the public role).

True

TRUE OR FALSE - All users are automatically members of the public standard database role.

False

TRUE OR FALSE - Although SQL Server can authenticate Windows User Groups and their members, it does not know the actual Windows username for each user within the group, so the application is unable to gather audit information at both the user level and the group level.

False

TRUE OR FALSE - Assigning a default database to a user in the SQL Server Login Properties dialog box (Server Instance > Security > Logins > New Login) automatically grants access to that database.

True

TRUE OR FALSE - Because SQL Server is an environment within the Windows Server system, one of your primary security concerns should be ensuring that the Windows Server itself is secure.

True

TRUE OR FALSE - By default, roles will be owned by the dbo user.

False

TRUE OR FALSE - Certain database fixed roles do not affect object access, such as the right to read to and write from the database.

False

TRUE OR FALSE - Deleting a SQL Server login also deletes the user from Windows.

True

TRUE OR FALSE - If a user has access to a database, then permission to the individual database objects may be granted.

False

TRUE OR FALSE - If multiple permission paths exist and one of the paths are denied, then the user may still access the object using another path in which the permission is marked as GRANTED.

True

TRUE OR FALSE - If you add a user to the sysadmin role group, that user must reconnect to the SQL Server instance in order for the full capabilities of the sysadmin role to take effect.

True

TRUE OR FALSE - Once users exist in the Windows user list or the Windows domain, SQL Server can recognize them.

True

TRUE OR FALSE - Permissions to databases may be granted either directly to the user --OR-- to a standard role, with the user then assigned to the role.

True

TRUE OR FALSE - SQL Server authentication is LESS secure than Windows login authentication; therefore, avoiding mixed mode is recommended.

False

TRUE OR FALSE - The Guest User account is automatically created when a database is created.

True

TRUE OR FALSE - Users who have not been granted direct access to a database can gain access using the "guest" user account—and with this account, they can make limited changes within the database server.

False

TRUE OR FALSE - When a user belongs to a fixed database role and has certain administrative level permissions, he or she is able to access all data without first being explicitly granted permission to database objects (e.g., tables, stored procedures, views, functions).

False

TRUE OR FALSE - When granting a user access to a database from within the "Database User - New" window, you can only grant the user access to the database in which you selected the "New User" GUI command.

True

TRUE OR FALSE - Windows Authentication is very robust in that it will authenticate not only Windows users, but also users within Windows user groups.

False

TRUE OR FALSE - Windows Authentication mode is less superior to mixed mode because mixed mode users need not learn yet another password and because this mode leverages the security design of the network.

False

TRUE OR FALSE - You can manage database access from the login side, but NOT the database side.

False

TRUE OR FALSE - You can modify the permissions assigned to a fixed server role.

DROP LOGIN 'COL\lfischer'

TSQL script command for deleting a SQL Server login. (uses Windows user info from Active Directory)

USE CodeEnforcement DROP USER 'lfischer'

TSQL script command for removing a user's database access; uses the database username only...unlike the CREATE statement, this statement does NOT use the server login as well.

USE CodeEnforcement CREATE USER 'COL\lfischer', 'lfischer'

TSQL script command which is use when assigning a user to a database.

GRANT All ON Contacts TO dbcreator

TSQL script command which is used when assigning ALL object permissions to a ROLE in a database.

GRANT Select, Update ON Emails to Guest, LRN

TSQL script command which is used when assigning MULTIPLE object permissions to a ROLE & a DATABASE USERNAME in a database.

GRANT Select ON MondayMorningMeeting TO lfischer

TSQL script command which is used when assigning a object permissions to a user in a database.

GRANT Select ON Email TO Joe WITH GRANT OPTION

TSQL script command which is used when assigning the WITH GRANT object permissions to a DATABASE USERNAME in a database.

Authentication

The act of establishing or confirming a user or system identity

User Account

These credentials are created at the DATABASE level; used when designating permissions to databases within the server.

Logins

These credentials are created at the SERVER INSTANCE level; does not equate access to databases within the server.

Databases, Tables, Logins, Users, & Roles

These objects can be granted permission to access "securables" within a database.

Fixed Server Roles

These role types grant permission to perform certain server-related administrative tasks

User-defined Server Roles

These server role types enable you to grant individual server permissions to a user which are not defined by a fixed (uneditable) server role.

db_backupoperator

This fixed database role allows a user to perform backups, checkpoints, and database console commands (DBCC) commands, but not restores. (Only server sysadmins can perform restores.)

db_datawriter

This fixed database role allows a user to write to all data in the database; this role is the equivalent of a grant on all objects, and it can be overridden by a deny permission.

db_accessadmin

This fixed database role authorizes a user to access the database, but not to manage database-level security.

db_ddladmin

This fixed database role authorizes a user to issue DDL commands (create, alter, drop).

db_datareader:

This fixed database role authorizes a user to read all data in the database; this role is the equivalent of a grant on all objects, and it can be overridden by a deny permission.

db_denydatawriter

This fixed database role blocks a user from modifying data in any table in the database; this overrides any object-level grant.

db_denydatareader

This fixed database role blocks a user from reading from any table in the database; this overrides any object-level grant.

Windows SID

Windows security identifier; this is passed to SQL Server when users utilize Windows Authentication to log on to the server.