fn_my_permissions

 fn_my_permissions ( securable , 'securable_class' )

Value	Description
DATABASE	Permissions on the current database
OBJECT	Table, View, Stored Procedure, etc.
SCHEMA	Database schema
SERVER	Server-level permissions

SELECT * FROM fn_my_permissions(NULL, 'DATABASE');

SELECT * FROM fn_my_permissions(NULL, 'SERVER');

SELECT * FROM fn_my_permissions('dbo.YourTableName', 'OBJECT');

PackageName	Source Table Name	Destination Table Name
ETL_ARCHIVE_FIN10_HTD_LOAD
ETL_ARCHIVE_FIN10_LFM_LOAD
ETL_ARCHIVE_FIN10_RTH_LOAD
ETL_ARCHIVE_FIN10_IDT_LOAD
ETL_ARCHIVE_FIN10_LDA_LOAD
ETL_ARCHIVE_FIN10_RTL_LOAD
ETL_ARCHIVE_FIN10_UPR_LOAD
ETL_ARCHIVE_FIN10_GET_LOAD
ETL_ARCHIVE_FIN10_RCT_LOAD
ETL_ARCHIVE_FIN10_SDRH_LOAD
ETL_ARCHIVE_FIN10_ATD_LOAD
ETL_ARCHIVE_FIN10_EAB_LOAD
ETL_ARCHIVE_FIN10_ALR_LOAD
ETL_ARCHIVE_FIN10_CMG_LOAD
ETL_ARCHIVE_FIN10_LRH_LOAD
ETL_ARCHIVE_FIN10_LRSH_LOAD
ETL_ARCHIVE_FIN10_ACPART_LOAD
ETL_ARCHIVE_FIN10_GAM_LOAD
ETL_ARCHIVE_FIN10_LRHH_LOAD
ETL_ARCHIVE_FIN10_LRS_LOAD
ETL_ARCHIVE_FIN10_ADT_LOAD
ETL_ARCHIVE_FIN10_ATO_LOAD
ETL_ARCHIVE_FIN10_COT_LOAD
ETL_ARCHIVE_FIN10_GAC_LOAD
ETL_ARCHIVE_FIN10_LDAH_LOAD
ETL_ARCHIVE_FIN10_LTDH_LOAD
ETL_ARCHIVE_FIN10_AED_LOAD
ETL_ARCHIVE_FIN10_ITC_LOAD
ETL_ARCHIVE_FIN10_LDTH_LOAD
ETL_ARCHIVE_FIN10_SMT_LOAD
ETL_ARCHIVE_FIN10_AAS_LOAD
ETL_ARCHIVE_FIN10_ACH_LOAD
ETL_ARCHIVE_FIN10_LAM_LOAD
ETL_ARCHIVE_FIN10_LDT_LOAD
ETL_ARCHIVE_FIN10_FBM_LOAD_ARCHIVE
ETL_ARCHIVE_FIN10_FAE_LOAD_ARCHIVE
ETL_ARCHIVE_FIN10_RTT_LOAD_ARCHIVE
ETL_ARCHIVE_FIN10_DDC_LOAD_ARCHIVE
ETL_ARCHIVE_FIN10_C_CRLIFE_LOAD_ARCHIVE
ETL_ARCHIVE_FIN10_OTC_LOAD_ARCHIVE
ETL_ARCHIVE_FIN10_RPT_LOAD
ETL_ARCHIVE_FIN10_CGM_LOAD
ETL_ARCHIVE_FIN10_FCI_LOAD
ETL_ARCHIVE_FIN10_HTH_LOAD
ETL_ARCHIVE_FIN10_SOL_LOAD

Monitoring SQL Server Deadlocks using Extended Events

Extended Events were first introduced in SQL Server 2008, and therefore you will not face any problem implementing them in our environment.

Extended Events is a lightweight performance monitoring feature that enables users to collect data to monitor and troubleshoot problems.

A deadlock is a situation where two or more processes or threads are blocked indefinitely, waiting for each other to release resources that they need to proceed.

Here we are going to observe how to gather deadlock information through Extended Events :

Creating SQL Server Extended Events to Capture Deadlocks using Management Studio

Step 1: First, open SQL Server Management Studio (SSMS) and navigate to Management > Extended Events > Sessions.

 

Step 2: Right-click on Sessions and select New Session.

Step 3: In the new window, provide a name for your event, we will call it SQLWATCH_deadlock in this case. Select the event to start at the beginning of the server and when the session is created, you can change these settings if necessary.

Step 4:  Go to the Events tab and in the event library textbox type “deadlock” to filter deadlock events:

Step 5: We will select 2 events: Lock_Deadlock (Raised when a request to acquire a lock is cancelled for the victim of a deadlock) and Lock_deadlock_chain (Raised when a request to acquire a lock results in a deadlock. This event is raised for all members of the deadlock).

Step 6: Having both the events chosen, click on the configure button, and a new window will be opened, in this window we will take a snapshot of the sql_text field so that we can view the query that generated the deadlock

Step 7: In the Data Storage tab, select where you want to store the Extended Event data use a file.

Step 8: After that, click on OK to save the Event configuration, we can see that the event is created and is already running.