SQL Server How to get application name which changed the data

Question

Suppose a user can login to db from SSMS and change data or a .Net application also can connect to db and change data. so there could be many .net application which changing data in few tables of same db. so how can i get the .net application names which changes data?

is it app_name?

IF APP_NAME() = 'azdata-Query'  
  PRINT 'Application: ' + APP_NAME() + char(10) + 'Date: ' + CONVERT ( varchar(100) , GETDATE(), 111);
ELSE IF APP_NAME() = 'Core .Net SqlClient Data Provider' 
  PRINT 'Application: ' + APP_NAME() + char(10) + 'Date: ' + CONVERT ( varchar(100) , GETDATE(), 103);

suppose i have two .net application which insert / update data in my db. one application name is chat which is winform app and another one is inventryx which is also winform application.

how can i get those name like inventryx or chat from APP_Name function?

please share idea how to capture application name or application exe name from SQL server end.

just i came to know that if i set Application Name property of connection string then we can get application name from APP_Name() function

https://www.mssqltips.com/sqlservertip/2897/using-the-sql-server-appname-function-to-control-stored-procedure-execution/
connectionString="Server=mySqlServer;Initial Catalog=myDatabase;User id=aUser;Password=aPassword;Application Name=My Application;"
Server=localhost;Database=SQLShackDemo;User Id=SuperHero;Password=1pass; Application Name=SuperApp;Workstation ID=SuperNova;Pooling=true; Connection Lifetime=100 

thanks

Answer 1

just i came to know that if i set Application Name property of connection string then we can get application name from APP_Name() function

Yes, you can pass an application name by connection string, but that's nothing reliable, because you can pass anything as app name, also in SSMS you can fake any name.

Answer 2

Hi TZacks-2728,
Please try to add the param Application Name=My app name to the connection string, and then run the following query to get the application name using the program_name column of sys.dm_exec_sessions:

SELECT      c.session_id, s.program_name, s.login_name, s.status, st.text  
FROM        sys.dm_exec_connections c  
INNER JOIN  sys.dm_exec_sessions s ON c.session_id = s.session_id  
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS st  
ORDER BY    c.session_id  

It works for me.

Best Regards,
Amelia

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.