Monitoring user activity in database

Since you're new to the game, I'll tell you that there is very little in the world of SQL Server that cannot be found in some "expert" article if you know what to look for in Google. Usually, it's pretty easy. For example, if you do the following Google search...

https://www.google.com/search?q=audit+all+user+activity+sql+server+2016&oq=audit+all+user+activity+sql+server+2016

... you'll find some really good information on the subject, especially in the first two non-paid listings. Here they are...

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine

Something else you should get into the habit of, especially on this forum, is that your first sentence of every post should state... "I'm using SQL SERVER xxxx yyyyy Edition" and change out the "xxxx" for the version that you are using and the "yyyyy" for the Edition because it makes a huge difference in how to approach all but the most basic problems because of all the improvements, deprecations, and discontinuations that MS has done to SQL Server over the years. .

Getting back to your original question, the subject of auditing is huge and has nearly as many facets to it as SQL Server itself. You first need to define what the real purpose of the auditing is than then define how you would find the data and analyze it. As Kristen stated, it can and frequently is like "looking for the proverbial needle-in-the-haystack". To make matters much worse, you have to remember the old rule of "if you measure it, you change it" and that can be especially true with auditing. You can, indeed, bring SQL Server to it's knees performance wise, memory wise, and disk space wise if you're not careful.

Take a look at the two links I posted and use the "Google Search" I gave you to find more. To be honest and with no slight intended, this isn't something that should be assigned to a beginner alone. BUT, a dedicated beginner can learn a shed-load of info about nearly every aspect of SQL Server by doing such a thing. Don't expect either to be simple or quick.

If you do need something quick, then maybe don't write your own. Instead, buy a product that will do it for you with the understanding that you'll then need to learn how to use THAT product. :wink:

Also, Google for "sp_WhoIsActive" and study it and how to use. It may be the first step for you, it's free, and it's written by one of the leaders of the industry.