Find out the Blocking tree in SQL Server. OR Head Blocker using script in SQL Server

Please find the below SQL Script to get Head blocker with Tree structure in SQL Server:

If exists (select * from #Temp_Table)

begin

Drop table #Temp_Table

end

else

SET NOCOUNT ON

GO

SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH

INTO #Temp_Table

FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T

GO

WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)

AS

(

SELECT SPID,

BLOCKED,

CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,

BATCH FROM #Temp_Table R

WHERE (BLOCKED = 0 OR BLOCKED = SPID)

AND EXISTS (SELECT * FROM #Temp_Table R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)

UNION ALL

SELECT R.SPID,

R.BLOCKED,

CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,

R.BATCH FROM #Temp_Table AS R

INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID

)

SELECT N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 1) +

CASE WHEN (LEN(LEVEL)/4 - 1) = 0

THEN 'HEAD - '

ELSE '|----- ' END

+ CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE

FROM BLOCKERS ORDER BY LEVEL ASC

GO

Output: Head blocker session id is 61. 

SQL Server - To check all logins and permission and other important details of SQL Server User.

Easy script-

To check all logins and permission and other details

SELECT

p.name as 'Name',

Case p.type

When 'S' Then 'SQL login'

When 'U' Then 'Windows login'

When 'G' Then 'Windows group'

When 'R' Then 'Server role'

When 'C' Then 'Login mapped to a certificate'

When 'K' Then 'Login mapped to an asymmetric key' End as 'Type',

Case sp.type When 'COSQ' Then 'CONNECT SQL' End as 'Permission Type',

sp.[state_desc] as 'Permission State',

p.is_disabled AS IsDisabled,

LOGINPROPERTY(p.name, N'IsExpired') AS IsExpired,

LOGINPROPERTY(p.name, N'IsLocked') AS IsLocked,

LOGINPROPERTY(p.name, N'IsMustChange') AS IsMustChange,

CAST(sl.is_policy_checked AS bit) AS PasswordPolicyEnforced,

CAST(sl.is_expiration_checked AS bit) AS PasswordExpirationEnabled,

LOGINPROPERTY(p.name, N'BadPasswordCount') AS BadPasswordCount,

LOGINPROPERTY(p.name, N'BadPasswordTime') AS BadPasswordTime,

LOGINPROPERTY(p.name, N'PasswordLastSetTime') AS PasswordLastSetTime,

LOGINPROPERTY(p.name, N'LockoutTime') AS LockoutTime,

LOGINPROPERTY(p.name, N'DaysUntilExpiration') AS DaysUntilExpiration,

p.create_date AS CreateDate,

p.modify_date AS LastModifieddate

FROM sys.server_principals AS p

LEFT OUTER JOIN sys.server_permissions AS sp ON sp.grantee_principal_id = p.principal_id

LEFT OUTER JOIN sys.sql_logins AS sl ON sl.principal_id = p.principal_id

WHERE

(sp.[type] = N'COSQ' and sp.type is not null)

and p.type not in ('R', 'C', 'K')

And p.name not like '##MS_%' --Filter out Certificate-based SQL Server Logins (https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/principals-database-engine)

Order By p.Type ASC, p.name ASC