April Hammond .net User Group Presentation - SQL Server Security and Permissions

Here's the slide deck and sample files from last night's presentation at the Hammond .net User Group.  We discussed SQL Server security principals, permissions and application authentication.  If you were in the audience, thanks for attending!

Download the slides and sample scripts here.

SQL Saturday Houston #107 - SQL Server Best Practices with DMV Presentation Files

Thanks for joining me for a standing room only presentation this morning at 9:45am at SQL Saturday #150 Houston.  This is the second year speaking at SQL Saturday Houston and the event was one of the best I've been to, kudos to the organizers.  I was especially impressed that all ten tracks were solidly SQL server related - very little was not related SQL Server, and that isn't easy to draw.  Bravo!

Click here to download the slidedeck and all sample scripts - including the ones we skipped - from my DMV presentation.  Thanks for all the great questions and feedback during the session, it was one of the most enjoyable I've had at a SQL Saturday event - including Baton Rouge's, and despite the fact that my presentation mouse batteries died!

SQL Script Toolbox for Developers

I work with a motley crowd of developers and have the opportunity to give lunch and learn training sessions on SQL Development.

Most recently, I put together a .zip file of T-SQL scripts that developers would find handy.  You can view the toolbox contents here, with .zip files included that are automatically generated.

Here's a summary:

Missing Indexes - missing indexes.sql, missing index setup demo.sql - more about this here
Index Usage - index usage.sql
Sessions and Requests - sessions and requests.sql - more about this here
Most expensive queries - worst queries.sql - from MS PSS
Tables without clustered indexes - tables without clustered indexes.sql
Deadlocks XEvents - deadlocks in xevents.sql -  more about this here
Dependencies - dependencies.sql -  more about this here
Stored Proc Execution plans - cached exec plans.sql -  more about this here
Volume Stats - volume stats.sql
Modules vs. Routines - modules vs routines.sql -  more about this here
Much more...

EDIT: 20131017 refreshed the toolbox.zip file with latest updates from my personal toolbox.
EDIT: 20140813 refreshed the toolbox.zip file with latest updates from my personal toolbox.
EDIT: 20140929 refreshed the toolbox.zip file with latest updates from my personal toolbox.
EDIT: 20180815 refreshed the Toolbox reference to Github repo

Using sys.dm_exec_requests to find a blocking chain and much more

As with every good DBA's toolbox, my blocking list query using sys.dm_exec_requests is evolving.

I've added the following features:

• The query execution plan of the active request in the QueryPlan column to the right.
• Variable at the top which changes the relationship of the join between sys.dm_exec_sessions and  sys.dm_exec_requests. 
• When set to 0, this query now displays all sessions, even those without active requests.  I recently found this helpful when researching sleeping sessions that were blocking active sessions.
• When set to 1, this query displays as it used to - only session and active request data.  
• Percent_Complete column - great for finding the progress of backup and restores 
• A few other minor helpful columns

Update March 4, 2014:

• Added Transaction Isolation Level for Session and Request
• Added Status for Session and Request
• Added Login_name, Client_Interface_name

Update June 24, 2016:

• Added resource governor group and pool identifiers
• Bugfix for sometimes when the offset text wouldn't display anything

 declare @showallspids bit 
 select @showallspids =1

 create table #ExecRequests  (
  id int IDENTITY(1,1) PRIMARY KEY
 , session_id smallint not null 
 , request_id int null
 , request_start_time datetime null
 , login_time datetime not null
 , login_name nvarchar(256) null
 , client_interface_name nvarchar(64)
 , session_status nvarchar(60) null
 , request_status nvarchar(60) null
 , command nvarchar(32) null
 , sql_handle varbinary(64) null
 , statement_start_offset int null
 , statement_end_offset int null
 , plan_handle varbinary (64) null
 , database_id smallint null
 , user_id int null
 , blocking_session_id smallint null
 , wait_type nvarchar (120) null
 , wait_time_s int null
 , wait_resource nvarchar(120) null
 , last_wait_type nvarchar(120) null
 , cpu_time_s int null
 , tot_time_s int null
 , reads bigint null
 , writes bigint null
 , logical_reads bigint null
 , [host_name] nvarchar(256) null
 , [program_name] nvarchar(256) null
 , Governor_Group_Id int null
 , blocking_these varchar(1000) NULL
 , percent_complete int null
 , session_transaction_isolation_level varchar(20) null
 , request_transaction_isolation_level varchar(20) null
 )


 insert into #ExecRequests (session_id,request_id, request_start_time, login_time, login_name, client_interface_name, session_status, request_status, command,sql_handle,statement_start_offset,statement_end_offset,plan_handle,database_id,user_id,blocking_session_id,wait_type,last_wait_type,wait_time_s,wait_resource,cpu_time_s,tot_time_s,reads,writes,logical_reads,[host_name], [program_name] , session_transaction_isolation_level , request_transaction_isolation_level , Governor_Group_Id)
 select s.session_id,request_id, r.start_time, s.login_time, s.login_name, s.client_interface_name, s.status, r.status,command,sql_handle,statement_start_offset,statement_end_offset,plan_handle,r.database_id,user_id,blocking_session_id,wait_type,r.last_wait_type, r.wait_time/1000.,r.wait_resource ,r.cpu_time/1000.,r.total_elapsed_time/1000.,r.reads,r.writes,r.logical_reads,s.[host_name], s.[program_name], s.transaction_isolation_level, r.transaction_isolation_level, s.group_id 
 from sys.dm_exec_sessions s 
 left outer join sys.dm_exec_requests r on r.session_id = s.session_id
 where 1=1
 and s.session_id >= 50 --retrieve only user spids
 and s.session_id <> @@SPID --ignore myself
 and  (@showallspids = 1 or r.session_id is not null) 

 print 'insert done'

 update #ExecRequests 
 set blocking_these = LEFT((select isnull(convert(varchar(5), er.session_id),'') + ', ' 
       from #ExecRequests er
       where er.blocking_session_id = isnull(#ExecRequests.session_id ,0)
       and er.blocking_session_id <> 0
       FOR XML PATH('') 
       ),1000)
 
 print 'update done'

 select * from 
 (
  select  
   timestamp = getdate()
  , r.session_id , r.host_name , r.program_name
  , r.session_status
  , r.request_status
  , r.blocking_these
  , blocked_by =  r.blocking_session_id
  , r.wait_type 
  , r.wait_resource
  , r.last_wait_type
  , DBName = db_name(r.database_id)
  , est.objectid
  , r.command
  , login_time
  , login_name
  , client_interface_name
  , request_start_time
  , r.tot_time_s, r.wait_time_s
  , r.cpu_time_s --cpu_time is not accurate prior to SQL Server 2012 SP2.  http://blogs.msdn.com/b/psssql/archive/2014/11/11/how-come-sys-dm-exec-requests-cpu-time-never-moves.aspx
  , r.reads, r.writes, r.logical_reads
  --, [fulltext] = est.[text]
  , offsettext = CASE WHEN r.statement_start_offset = 0 and r.statement_end_offset= 0 THEN left(est.text, 4000)
         ELSE SUBSTRING (  est.[text]
             , r.statement_start_offset/2 + 1, 
              CASE WHEN r.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), est.[text])) 
               ELSE r.statement_end_offset/2 - r.statement_start_offset/2 + 1
              END )
       END
  , r.statement_start_offset, r.statement_end_offset
  , cacheobjtype = LEFT (p.cacheobjtype + ' (' + p.objtype + ')', 35)
  , QueryPlan  = qp.query_plan 
  , request_transaction_isolation_level = case request_transaction_isolation_level 
    when 0 then 'Unspecified'
    when 1 then 'ReadUncommitted'
    when 2 then 'ReadCommitted'
    when 3 then 'Repeatable'
    when 4 then 'Serializable'
    when 5 then 'Snapshot' end 
  , session_transaction_isolation_level = case session_transaction_isolation_level 
    when 0 then 'Unspecified'
    when 1 then 'ReadUncommitted'
    when 2 then 'ReadCommitted'
    when 3 then 'Repeatable'
    when 4 then 'Serializable'
    when 5 then 'Snapshot' end 
  , p.plan_handle
  , stat.execution_count, total_worker_time_s = stat.total_worker_time/1000./1000., last_worker_time_s = stat.last_worker_time/1000./1000., total_elapsed_time_s = stat.total_elapsed_time/1000./1000., last_elapsed_time_s = stat.last_elapsed_time/1000./1000., stat.total_physical_reads, stat.total_logical_writes, stat.total_logical_reads
  , Governor_Group_Name = wg.name
  , Governor_Group_ID  = r.Governor_Group_Id
  , Governor_Pool_Name = wp.name 
  , Governor_Pool_ID  = wg.Pool_id
  --next two lines are SQL 2012 only!
  --, stat.total_rows, stat.last_rows
  from #ExecRequests r
  LEFT OUTER JOIN sys.dm_exec_cached_plans p ON p.plan_handle = r.plan_handle 
  OUTER APPLY sys.dm_exec_query_plan (r.plan_handle) qp
  OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) est
  LEFT OUTER JOIN sys.dm_exec_query_stats stat on stat.plan_handle = r.plan_handle
  and r.statement_start_offset = stat.statement_start_offset  
  and r.statement_end_offset = stat.statement_end_offset
  LEFT OUTER JOIN sys.resource_governor_workload_groups  wg
  on wg.group_id = r.Governor_Group_Id
  LEFT OUTER JOIN sys.resource_governor_resource_pools wp
  on wp.pool_id = wg.Pool_id
  
 
 ) a
 order by LEN(blocking_these) desc, blocking_these desc, blocked_by desc, session_id asc
 
 print 'done'
 go
 drop table #ExecRequests  
 

sys.sql_modules vs. INFORMATION_SCHEMA.Routines

Consider the following two queries meant to search for a keyword in SQL programmability objects.

select s.name +'.' + o.name, o.type_desc, m.definition
from sys.sql_modules m
inner join sys.objects o on m.object_id = o.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
where definition like '%dataserver2%'
order by o.name

select r.SPECIFIC_SCHEMA + '.' + r.SPECIFIC_NAME, r.routine_type, r.ROUTINE_DEFINITION
from INFORMATION_SCHEMA.routines r
where ROUTINE_DEFINITION like '%dataserver2%'
order by ROUTINE_NAME 

While the INFORMATION_SCHEMA is ANSI compliant and therefore may be more familiar to Oracle or IBM folks, I recommend using sys.sql_modules instead.  

I'll throw a LEN(m.definition) and LEN(r.ROUTINE_DEFINITION) on those queries and show:

Same results out of the search, but ROUTINES returns the first 4000 characters, as opposed to the output of sys.sql_modules which is an nvarchar(max). 

What's most dangerous about that?  If you're searching for a string in your database objects and that string happens to be in line past the 4000-character mark, a query on INFORMATION_SCHEMA will fail to return the object as a match!

Consider these two queries on the SQL 2012 RTM ReportServer database:

select s.name +'.' + o.name, o.type_desc, m.definition
from sys.sql_modules m
inner join sys.objects o on m.object_id = o.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
where m.DEFINITION like '%            AND OwnerID = @OwnerID%'
order by o.name;

select r.SPECIFIC_SCHEMA + '.' + r.SPECIFIC_NAME, r.routine_type, r.ROUTINE_DEFINITION
from INFORMATION_SCHEMA.routines r
where r.ROUTINE_DEFINITION like '%            AND OwnerID = @OwnerID%'
order by ROUTINE_NAME; 

Here's the result sets:

Yikes!

Consider also this cryptic, foreboding warning all over MSDN:

I prefer to stick with my sys objects instead of INFORMATION_SCHEMA when searching code.

Oh, and sys.syscomments is deprecated.

More info at sys.sql_modules.

Certification Exams: From the Other Side

Today I took the 70-464 beta exam today "Developing Microsoft SQL Server 2012 Databases" and after spending the last few months as an item writer for two other SQL 2012 exams, my perspective on the experience is different.  Having taken numerous SQL 2000, 2005 and 2008 exams, this was my first exam with the benefit of my experience as an item writer.

I obviously can't talk about specifics regarding the exams, and I'll say even less than I'm allowed.

The following are lessons I learned from the experience of being on the item writing team for a certification exam.  I believe these lessons can apply to any technology, for any level of certification exam.

• The wrong answers are more difficult to perfect than the correct answer.  
• Creating challenging wrong answers sometimes involves introducing new variables or twists to your answer, and it is difficult to keep the answer simple.  (More on that later.)
• The right answer is easy, and the polar opposite answer is easy.  But two other wrong answer vectors really is more complicated than you think.  
• We can't make stuff up.  Every answer you see is something real.   The correct answer has to be 100% correct.  The "distractors" have to be plausible. 
• Love your grammar nazi.  
• The reviewer you work with knows nothing about SQL, but everything about proper word choice, Microsoft's preferred phrasing, acceptable acronyms and abbreviations and shorthands.
• It can be pretty frustrating the first time your question gets ripped to shreds because of word issues - not technical issues. Learn from it and try to understand why.  I think I've finally gotten the hang of all the gotchas... just in time to be done.
• You "need to..." not "You must" 
• You "should use" not "could use" or "would use"
• "By using" not "using"
• "Named" not "called"
• "You are developing" not "you are working on"
• "by using the least amount of administrative effort" not "most efficiently" or "best"
• "server that runs SQL Server" not "running SQL"
• If the word doesn't translate well, don't use it.
• The word "only" is a pain in the ass.  
• The "application only connects to one database" vs. the "application connects to only one database" is a big difference.  Think about it.
• "Best practices" are a dangerous minefield.  Just because you as the item writer have always done something one way, doesn't mean it is the only correct way.
• A question cannot rely on "industry standard" practices, but correct and incorrect processes.
• "Least administrative effort" questions need to be airtight - there can be no room for argument.
• When it comes to writing a question, keep it simple.
• A good question tests your grasp of one or two critical pieces of product knowledge.
• A bad question tries to pull in too many different variables or factors and ends up with awkward wording, overly verbose answer text or a non-realistic question.  
• I commented today on a question that obviously grew out of control during the writing process and ended up being unrealistic, with all of the answers undesirable options in an obscure scenario.
• You're given assignments in certain categories of the Objective Domain to write in.  Questions take on a life of their own when you're writing.  Gotta keep on topic, and as a question evolves it is easy to end up with a question in the wrong Objective Domain category.
• Sometimes as the item writer you'll end up writing one question and finishing up another.  Veer off your assignment and you'll find yourself bargaining with the other writers to swap. 
• I only did this once throughout the entire process, and the other writer was understanding and polite.  He let me swap one of my questions that had inadvertently drifted off topic. I took on one of his in a different category, and saved a good question and a lot of effort.  
• It is stressed as an item writer to make sure your question is bound technologically to the Objective Domain category you're writing for.
• Build lists are probably not worth the trouble.
• I developed a couple build lists but they quickly become complicated.  I think they turned out okay, but they were time consuming to nail down a finite, concrete order.
• Build Lists must have one right order.  It is too easy to put unrelated things in the build list that are not dependent.  Build lists give the most problems at alpha. 
• Sure enough, on today's beta exam, I'm 99% sure that one of the Build List questions was broken, by an unlinked chain of events... something that made logical sense but wasn't actually dependent.
• The comments on beta exams are not always... helpful.
• Reviewing beta comments on some of the SQL 2008 exams while on a SME engagement in Redmond was humorous.  Rudely insisting that a question is flawed when you missed the critical bit of info is pretty embarrassing for you on the non-anonymous comment report.
• Oftentimes a question would draw a string of "Answer choices B and C are identical!!!!!!!!1111!!!!!" when there was in fact a significant but tough-to-notice difference. 
• I kept that in mind today when commenting on problems in beta questions. I found a number of typos and incorrect lines, but I withheld the snark and the sarcasm.  No sense in being a jerk about it and making a fool of yourself.
• SME is not "I'm the best person my boss knows" or "I'm pretty good at this".  SME is "I'm probably not going to embarrass myself in front of another SME."