Failed sysadmin Permission Elevation Attempts

Consider this scenario.
 
You have a user called Bob who thinks today is a good day to become a sysadmin.
 
He runs the following command to escalate his privileges:
 
ALTER SERVER ROLE [sysadmin] ADD MEMBER [bob];
 
Thankfully this fails and world domination will need to wait another day.
 
image1
Now as a DBA you’ll be thinking, this activity will be all over the SQL ERRORLOG and/or Windows Event logs so you can have a chat with Bob about his intensions, just as you would with login failures.
 
Allais, there is nothing recorded in either of these.
 
Fortunately, the Default Trace does capture this event and you can have that chat with Bob after all.
 

SELECT StartTime, TextData, ApplicationName, LoginName
,CASE WHEN a.Success = 0 THEN 'No' ELSE 'Yes' END AS 'Was Successful'
FROM master.sys.fn_trace_gettable(
( SELECT REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),256)) + 'log.trc'
FROM  sys.traces
WHERE is_default = 1
), DEFAULT) a
WHERE a.Success = 0

 
image2

Brain teaser

0x57656C6C2D646F6E6520666F7220666967757
2696E67206F75742074686520736F6C7574696F
6E2E20546869732077617320746F2064656D6F6
E73747261746520746861742062696E61727920
6973206A757374206F6E65206F66206D616E792
07761797320746F2073746F726520646174612E
2054686520616E73776572206973203432203A29

xp_readerrorlog Msg 22004

When using xp_readerrorlog you may receive the error message:
 
Msg 22004, Level 12, State 1, Line 0
Error executing extended stored procedure: Invalid Parameter Type

 
image1
Pre-SQL Server 2012 this does work.
image2
From SQL Server 2012 onwards you require the N prefix:
image3

SQL Server Product Key

Have you installed SQL Server and wondered how the product key appears to magically populate without you doing anything e.g.
 
image1
 
This info is obtained from the DefaultSetup.ini file on the installation media and the product key doesn’t auto populate if the file doesn’t exist.
 
image2

Changes to SQL Server configuration options

Continuing on from the previous post on sys.databases, DBAs may/may not have noticed the adds/removes/tweaks occurring behind the scenes as each version changes.
 
A nice one of note is the allowing the backup checksums by default from SQL Server 2014.
 
Below is a breakdown of sys.configurations changes since SQL Server 2005.
 
SQL Server 2008

Setting Change
Web Assistant Procedures Removed
access check cache bucket count Added
access check cache quota Added
affinity64 mask Added
affinity64 I/O mask Added
backup compression default Added
EKM provider enabled Added
filestream access level Added
optimize for ad hoc workloads Added
max text repl size (B) Maximum value changed from 0 to -1
min server memory (MB) Default value changed from 8 to 16

 
SQL Server 2008 R2

Setting Change
max degree of parallelism Maximum value changed from 64 to 1024

SQL Server 2012

Setting Change
awe enabled Removed
SQL Mail XPs Removed
contained database authentication Added
EKM provider enabled Is dynamic changed from 0 to 1
max degree of parallelism Maximum value changed from 1024 to 32767
max server memory (MB) Minimum value changed from 16 to 128
max worker threads Is dynamic changed from 0 to 1
max worker threads Maximum value changed from 32767 to 65535
remote login timeout (s) Default value changed from 20 to 10

 
SQL Server 2014

Setting Change
backup checksum default Added

 
SQL Server 2016

Setting Change
allow polybase export Added
automatic soft-NUMA disabled Added
external scripts enabled Added
hadoop connectivity Added
polybase network encryption Added
remote data archive Added

 

SQL Server vNext (CTP 1.3)
No changes at this time.

Continual Evolution of SQL Server sys.databases

With each version of SQL Server the list of new features grows and keeping pace with them as a DBA can be a challenge.
 
As an example, the addition of new columns in sys.databases since SQL Server 2005 has been slowly climbing:
 

SQL Server 2008/R2
is_cdc_enabled
is_encrypted
is_honor_broker_priority_on

 

SQL Server 2012
replica_id
group_database_id
default_language_lcid
default_language_name
default_fulltext_language_lcid
default_fulltext_language_name
is_nested_triggers_on
is_transform_noise_words_on
two_digit_year_cutoff
containment
containment_desc
target_recovery_time_in_seconds

 

SQL Server 2014
is_auto_create_stats_incremental_on
is_query_store_on
resource_pool_id
delayed_durability
delayed_durability_desc

is_memory_optimized_elevate_to_snapshot_on

 

SQL Server 2016
is_federation_member
is_remote_data_archive_enabled
is_mixed_page_allocation_on

 

SQL Server vNext (CTP 1.3)
is_temporal_history_retention_enabled