Security
"Dad, Can I have the..."
HADR
Tee Sea Qwill
Grab Bag
100
If your user is granted read rights to a table, but denied read rights to the table's schema, what are your effective rights?
What is Denied
100
The constraint that uniquely identifies each row in a table
What is a PRIMARY KEY
100
This HADR strategy is built on top of a Windows Cluster, but unlike a Clustered SQL Instance does not require shared disk
What is AlwaysOn Availability Groups
100
This is required if you need the rows in your select to be returned in sequence.
What is ORDER BY
100
One of the two tools that will record SQL statements executed against the server for later review
What is SQL Trace (profiler) or Extended Events
200
Logins assigned to this role never have their permissions checked
What is sysadmin
200
The number of primary key constraints that a table may have.
What is 1
200
This HADR solution is deprecated and largely superseded by AlwaysOn Availability Groups
What is Mirroring
200
If selects against your view are performing poorly, consider converting it to this, assuming changes to the data underneath the view are infrequent.
What is an INDEXED VIEW
200
The word that starts a CTE
What is WITH
300
If you want to prevent a table's schema from being modified, you should create one of these.
What is a DDL Trigger
300
Unless you specify otherwise, creating a primary key also creates this database object.
What is a Clustered Index
300
This feature sends data to a secondary server at a sub-table level, meaning you can restrict which rows and columns are received by the secondary.
What is Snapshot, Transactional or Merge Replication
300
Use this join between the patient and address tables when you want all of the patient table rows, even those that don't have an address record.
What is OUTER JOIN (LEFT OUTER JOIN or RIGHT OUTER JOIN)
300
Examine this to determine if an index was used during the execution of your query
What is a Query Plan
400
The DevMgr user has execute rights on the AddData stored procedure in your database. You want to remove the execute rights, but still allow DevMgr to execute the procedure if DevMgr is a member of a group that has execute permissions. What would you put into the blank below to accomplish this?
______ EXECUTE ON dbo.AddData TO DevMgr
What is Revoke
400
The property that must be set on every column that is part of a primary key
What is NOT NULL
400
One of two HADR techniques that have a configurable latency period - which is great if you accidentally delete a table from your primary system and want to recover it from your secondary.
What is a Log Shipping or Snapshot Replication
400
WARNING: TRICK QUESTION This isolation level does not honor exclusive locks
All isolation levels honor exclusive locks.

Read Uncommitted (a common incorrect answer) doesn't request shared locks.
400
Daily Double!
Be sure to prevent this from occurring if you allow end user input to be dynamically concatenated to your SQL string. (Also humorously lampooned by "Little Bobby Tables" in an xkcd comic)
500
Your SQL Server instance shuts down because your server audit could not write to its log. What must you do to restart the server?
What is add disk space, start in single user mode (-m) or start with minimal configuration (-f)
500
If you use this keyword when creating a foreign key, deleting the primary record will also cause any foreign key records to be deleted.
What is CASCADE
500
Daily Double
This HADR solution allows any row to be updated on either the primary or secondary server.
500
The operand used between two complete and independent select queries to return only the rows that exist in both.
(Also the name of the special computer used by Chuck Bartowski)
What is INTERSECT
500
One of the two characters in addition to percent (%) and open/close brackets ([]) that have special meaning in a LIKE statement
What is _ or ^
M
e
n
u