What are users executing using SSMS?
During a customer review meeting, one of the DBA asked if there’s a quick way to find out What are users executing using SSMS. Though this information can easily be obtained from SP_Who2 system stored procedure, the DBA wanted to limit the output to only those users that would be executing commands only using SSMS.
Further to that, DBA asked if it’s possible to identify the IP address or Machine name of the user from where the query is being executed.
We were able to quickly write a small query that would provide exactly that. This query can be used to identify what are users executing using SSMS
Note: You can simply change the WHERE clause from the below code to some other application as well.
SQL Server Best Practices Script provided by: HTTPS://SQLOPS.COM (The Most Advanced Risk and Health Audit for Production Databases)
-- Author: Https://sqlops.com -- Most Advanced Risk and Health Audit for Production Databases SELECT SQLOPS_DMEXECCONNECTIONS.client_net_address as CLIENT_IP, SQLOPS_DMEXECSESSIONS.host_name as MACHINE_NAME, SQLOPS_DMEXECSQLTEXT.text as COMMAND FROM sys.dm_exec_sessions SQLOPS_DMEXECSESSIONS INNER JOIN sys.dm_exec_connections SQLOPS_DMEXECCONNECTIONS ON SQLOPS_DMEXECSESSIONS.session_id = SQLOPS_DMEXECCONNECTIONS.session_id CROSS APPLY sys.dm_exec_sql_text(SQLOPS_DMEXECCONNECTIONS.most_recent_sql_handle) SQLOPS_DMEXECSQLTEXT WHERE SQLOPS_DMEXECSESSIONS.program_name LIKE 'Microsoft SQL Server Management Studio%' ORDER BY SQLOPS_DMEXECSESSIONS.program_name, SQLOPS_DMEXECCONNECTIONS.client_net_address
Note: Above practice is just one of the several thousands of audit checks performed by SQLOPS Risk and Health Assessment for Production Databases. Your investment is worth every penny! 100% risk-free with the money back guarantee.
