How to set shortcuts in query editor

If we set short cuts for commands like sp_lock, sp_helptext , sp_who2 it can be handy as its
frequently required for any developers.
Lets see how to set short cut for this in query editor.

Go to Tools-> Option->Environemnt->Keyboard->Query Shortcuts

Set shortcuts here for your favorite and frequently used commands.

   

Dm_db_stats_properties - to get statistics properties

Managing statistics is important part for developers. Many times we have seen
improper statistics screwed up the queries and queries which completes in seconds
takes hours to complete.
Monitoring statistics and updating it regularly is important for this aspect.
For this we can use  sys.dm_db_stats_properties which give better insight about
each stats for a table.

This DMV has two arguments

Object_id
Stats_id

Better we should user cross apply it with sys.stats and it will give up
in depth details about the stats.

SELECT s.object_id,

       Object_name (s.object_id),

       s.stats_id,

       s.auto_created,

       s.user_created,

       s.no_recompute,

       s.has_filter,

       s.is_temporary,

       s.is_incremental,

       d.last_updated,

       d.rows,

       d.rows_sampled,

       d.steps,

       d.unfiltered_rows,

       d.modification_counter

FROM   sys.stats s

       JOIN sys.tables t

         ON s.object_id = t.object_id

       CROSS apply sys.Dm_db_stats_properties(s.object_id, s.stats_id) d

WHERE  t.type = 'u'

How to audit login

Many times we need to check users logged in our system for various reasons.
This reasons can be many but main requirement we need to know who logged in out system.
There are many ways in SQL Server for this and with introduction of newer versions
many new methods are available for this .

Lets see some of this.1
1.Using SSMS
Connect to server , go to properties.
Go to Security -> Login Auditing.
Select "Both failed and successful logins"

Now restart the server.
You can see the details in errorlog.



2.Using Profiler 
Connect to profiler.
Select events
"Audit Login"
"Audit Login Failed"
Start the trace.
You can see the detail in profiler trace.

Error 8623 and 8632 in SQL Server 2016.

Recently after migrating to SQL Server 2016 one of our app start to failing.
While debugging I found the query which was failing.
The query is like this.

This was working fine SQL Server 2014 but now failing in SQL Server 2016.
I tried it on various serves where SQL Server 2016 is installed.
Getting either 8623 or 8632 error.

BOL give description for this as below.

Error 8623:
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

Error 8632:
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

It says we  need to change the query to avoid such large number of values in IN clause.
We need to use join in this case.

Workaround 
As its  third party app  and we can not modify the query immediately
We changed compatibility level to 120 (SQL Server 2014) and the query worked !!

This gives us time to contact app developer and get the app modified with new
query.

PWDCOMPARE - to check predictable password

How to check week passwords?
As a strong security policy we also need that password are not predictable.
Many time we need to test this but checking all such predictable password with all logins
is very tedious task and any one will try to avoid.

Recently I had a issue where I need to check that certain predictable passwords are not used by
any login.

Here we can use pwdcompare function , which solves this purpose very easily.

It checks password with stored password hash from sys.sql_logins.

I want to check which logins are using password like qwedsa or password

SELECT *

FROM sys.sql_logins

WHERE PWDCOMPARE('qwedsa',password_hash) = 1

  OR PWDCOMPARE('password',password_hash) = 1

Once we know this logins we can ask the user to change password with strong combinations.