Create
Learn
Share

SQL Database Administration Fundamentals

rename
jeaninem71's version from 2017-03-24 20:45

DDL (Data Definition Language) Statements

Question Answer
USEChanges the database context
CREATECreates a SQL Server database object (table, view, or stored procedure)
ALTERChanges an existing object
DROPRemoves an object from the database
TRUNCATERemoves ALL rows from a table and does not log the deletion
memorize

DML (Data Manipulation Language) Statements

Question Answer
SELECTRetrieves rows from the database and enables the selection of one or many rows or columns from one or many tables in SQL Server
INSERTAdds one or more new rows to a table or a view in SQL Server
UPDATEChanges existing data in one or more columns in a table or view
DELETERemoves rows from a table or view
memorize

Fixed Server Roles

Question Answer
BulkadminCan perform bulk insert operations
DbcreatorCan create, alter, drop, and restore databases
DiskadminCan create, alter, and drop disk files
ProcessadminCan kill a running SQL Server process
SecurityadminCan manage the logins for the server
ServeradminCan configure the server-wide settings, including setting up full-text searches and shutting down the server
SetupadminCan configure linked servers, extended stored procedures, and the startup stored procedure
SysadminCan perform any activity in the SQL Server installation, regardless of any other permission setting. The sysadmin role even overrides denied permissions on an object
memorize

Fixed Database Roles

Question Answer
db_accessadminAuthorizes a user to access the database, but not to manage database-level security
db_backupoperatorAllows a user to perform backups, checkpoints, and DBCC commands, but not restores. (Only server sysadmins can perform restores.)
db_datareaderAuthorizes 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_datawriterAllows 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_ddladminAuthorizes a user to issue DDL commands (create, alter, drop)
db_denydatareaderBlocks a user from reading any table in the database. This overrides any object-level grant
db_denydatawriterBlocks a user from modifying data in any table in the database. This overrides any object-level grant
db_ownerThis is a special role that has all permissions in the database. This role 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 this role
db_securityadminPermits a user to manage database-level security -- including roles and permissions
memorize