T-SQL Tuesday # 157: Having fun with T-SQL

April 11, 2023

I thank Reitse Eskens (Bl og|Twitter) for hosting T-SQL Tuesday #161. The topic for this month is Having Fun with T-SQL. I also want to acknowledge Adam Machanic’s vision behind this project. I learned a lot from some T-SQL Tuesdays and participated in a few. Thank you, Steve Jones (Blog|Twitter), for coordinating this program. If you are interested to see previous topics, visit here.

Reitse asked us about our “most fun script”. Reitse gave two examples. One he wrote, and another one came from Brent Ozar. I have two queries that I have been carrying in my library since 2014. Unfortunately, I did not document the source. If any of the readers know the writer’s name please let me know via comment, and I will add where credit belongs.

The first script is called “who can fire you”. This gives a list of members of Server-level roles.

Who can fire you?

SELECT 
	SR.[name] AS role__name,
	[server_principals].name AS login__name
FROM sys.[server_role_members] 
INNER JOIN sys.[server_principals] 
ON [server_role_members].[member_principal_id] = [server_principals].[principal_id]
INNER JOIN 
	(
		SELECT 
			[principal_id], 
			name
		FROM sys.[server_principals]
		WHERE [type_desc] = 'SERVER_ROLE'
  ) AS SR ON [server_role_members].[role_principal_id] = SR.[principal_id]
ORDER BY SR.name, [server_principals].[name];

This script is called “Abusing SSMS(Maybe)?”. Gives a list of request that is executing in SQL Server using SQL Server Management Studio excluding session running the script.

Abusing SSMS(Maybe)?

SELECT 
	SUBSTRING(ST.text, R.statement_start_offset / 2,
	( 
		CASE WHEN R.statement_end_offset = -1 THEN DATALENGTH(ST.text)
      ELSE R.statement_end_offset
    END - R.statement_start_offset ) / 2) AS statement_executing,
		S.session_id,
    S.login_time,
    R.start_time,
    S.host_name,
    S.login_name,
    db_name(R.database_id) AS database__name
FROM sys.dm_exec_sessions S 
INNER JOIN sys.dm_exec_requests R ON S.session_id = R.session_id
CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) ST
WHERE S.program_name LIKE 'Microsoft SQL Server Management Studio%' 
AND R.session_id <> @@SPID

Thanks for reading.