T-SQL Tuesday # 157: Having fun with T-SQL
April 11, 2023
I thank Reitse Eskens (Blog|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.
![](https://sqlworldwide.com/wp-content/uploads/2016/06/TSQL2SDAY-300x300.png)
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.