What are users executing from SSMS

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.

Credits: https://sqlops.com/sql-server-risk-assessment/

Explore our range of trailblazer services

Risk and Health Audit

Get 360 degree view in to the health of your production Databases with actionable intelligence and readiness for government compliance including HIPAA, SOX, GDPR, PCI, ETC. with 100% money-back guarantee.

DBA Services

The MOST ADVANCED database management service that help manage, maintain & support your production database 24×7 with highest ROI so you can focus on more important things for your business

Cloud Migration

With more than 20 Petabytes of data migration experience to both AWS and Azure cloud, we help migrate your databases to various databases in the cloud including RDS, Aurora, Snowflake, Azure SQL, Etc.

Data Integration

Whether you have unstructured, semi-structured or structured data, we help build pipelines that extract, transform, clean, validate and load it into data warehouse or data lakes or in any databases.

Data Analytics

We help transform your organizations data into powerful,  stunning, light-weight  and meaningful reports using PowerBI or Tableau to help you with making fast and accurate business decisions.

Govt Compliance

Does your business use PII information? We provide detailed and the most advanced risk assessment for your business data related to HIPAA, SOX, PCI, GDPR and several other Govt. compliance regulations.

You May Also Like…