Run SQL Management Studio as different user

It is helpful sometimes that I have a documentation how to login as different user  from SSMS. It is come in handy.

Press Shift key + Right click on SSMS shortcut
Click on Run as different user

Enter domain\login and password

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID {FDC3723D-1588-4BA3-92D4-42C430735D7D} and APPID {83B33982-693D-4824-B42E-7196AE61BB05}

To fix the error from Event Viewer, I refered to MS website

https://connect.microsoft.com/SQLServer/feedback/details/780569/mdw-job-on-mdw-server-generates-event-10016-distributedcom-error-in-system-log

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID 

{FDC3723D-1588-4BA3-92D4-42C430735D7D}

 and APPID 

{83B33982-693D-4824-B42E-7196AE61BB05}

1)    Run Dcomcnfg.exe 
2)    Expand Component Service -> My Computer ->DCOM Config
3)    Locate “Microsoft SQL Server Integration Services 11.0”
4)    Right click and select “Properties”
5)    Select “Security” tab
6)    In Launch and Activate Permissions, click on “Edit”
7)    Grant “Local Launch”, “Local Activation” permissions for SQL Agent Service account 
8)    Restart all Data collectors, Check Windows System Event log for any DCOM errors that you were seeing before.

DeadLock Graph from Extended Events

In SQL Server 2012, Deadlock graph is captured in system_health by default.

Under Management, right click on package0 under system_health, View target Data

Filtered by name contains xml

In SQL Server 2008 R2

Deadlock events can be queried from dynamic management views. I use the following query to get deadlock graph.

;with SystemHealth
As(
Select cast(target_data as xml)as sessionxml
from sys.dm_xe_session_targets st
inner join sys.dm_xe_sessions s on s.address = st.event_session_address
where name ='system_health'
)
Select Deadlock.value('@timestamp','datetime')as DeadlockDateTime
,cast(Deadlock.value('(data/value)[1]','varchar(max)') as xml)as DeadlockGraph
Into uat.dbo.dba3
from systemhealth
Cross apply sessionxml.nodes('//RingBufferTarget/event')as T(deadlock)
where deadlock.value('@name','nvarchar(128)') ='xml_deadlock_Report'

Enjoy my script.