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.