From SQL 2008 to SQL 2019

Question

My organization has migrate the instance I use from SQL 2008 to SQL 2019.
Since then a query I run to get the users that are using the different database in the instance just work for one of them

 SELECT sdes.nt_user_name as Utilizador  
 ,MIN(convert(datetime2(0), sdes.login_time)) as Entrada  
 ,MAX(convert(datetime2(0), sdes.last_request_start_time)) as UltimoRequest  
,sdes.host_name as Workstation FROM sys.dm_exec_sessions AS sdes  
 INNER JOIN sys.dm_exec_connections AS sdec ON sdec.session_id = sdes.session_id  
 CROSS APPLY ( SELECT DB_NAME(dbid) AS DatabaseName FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle) ) sdest  
 WHERE sdes.session_id <> @@SPID AND sdest.DatabaseName ='OneDatabase' GROUP BY sdes.nt_user_name, sdes.host_name;  

I think that the question is around the most_recent_sql_handle but I'm not sure

Can someone help me so I can question the DBA.

Thanks in advance

Joao

Answer 1

Thank you for reply.

For one of the database (lets call it XPTO) in the instance I get results for the other ones I don't.

I simplify the query for this one bellow

 select sdes.*
  , sdec.*
  , sdest.*
  FROM sys.dm_exec_sessions AS sdes 
  INNER JOIN sys.dm_exec_connections AS sdec ON sdec.session_id = sdes.session_id
  cross apply ( SELECT DB_NAME(dbid) AS DatabaseName FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle) ) sdest;

The field Databasename just shows data for the database XPTO and for the master and msdb.

For the other databases in the instance gives the value NULL

João

Answer 2

I would guess it is a permissions issue. Are you able to access all the databases? And more importantly, are you able view the definition of stored procedures etc?

If you run

SELECT * FROM sys.fn_my_permissions(NULL, NULL)

what do you see?

Answer 3

Hi @Joao ,

>I think that the question is around the most_recent_sql_handle but I'm not sure

It seems not. sys.dm_exec_sql_text returns the text of the SQL batch that is identified by the specified sql_handle. The sql_handle obtained from sys.dm_exec_connections is most_recent_sql_handle column.

This is the result of query from my environment.

It only shown one user database node2. It seems query result only show one user database. You need to modify the query.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.