0

I have what is probably a basic question, but I'm not getting anywhere searching/trying it on my own.

What I'm trying to accomplish: run a trace against a SQL DB used by Dynamics NAV and get a specific user's activity. (Context/End goal is to figure out what permissions a different user is missing.)

I've tried running traces with different templates, including a custom one I made to try to get what I want. However, all the activity captured is just for the user I am running the trace as (the admin account). If I filter using LoginName or NTUsername, I get nothing because it isn't capturing that data apparently.

How can I actually capture another user's actions? Any help is greatly appreciated. I've been using MS's SQL Server Profiler to run the trace. SSMS version is 15.0.18183.0.

Editing to add a screenshot: SQL Profiler Events Selection - I have all columns selected

Editing again: this was helpful. I can see other user activity but it's not what I'm expecting. I'm thinking what's happening is that all the actual database activity is being handled through the NAV app using the 2 admin principals that I can see, which would be why I can't see NAV app user principals in the Profiler results. Maybe I am wrong, but I can't see any other explanation for why I can't see their activity.

2
  • It should capture all running queries based on the criteria selected in the Trace Properties. Maybe adding a screenshot of the Event Selection screen would be helpful. You should start out with no filters applied, to be sure you're seeing a wider opened view.
    – J.D.
    Jul 7, 2023 at 16:25
  • 1
    Okay, I think that helped. This time I was able to see other users' activity at least(though it was other admin accounts), so hopefully if I make sure someone is doing certain activities live, I should be able to see those events. Thank you :) Jul 11, 2023 at 16:57

2 Answers 2

0

Converting my comment to answer since it appears to have helped:

Your screenshot shows that you're only profiling executions of stored procedures and not other queries such as raw SQL statements. You'll want to add SQLBatch:Completed and SQLBatch:Starting to capture those.

Also, if what you're trying to trace is specifically stored procedures, the profiler won't display them until they've finished executing. So if the procedure is long running, you won't see it immediately.

0

profiler deprecated long time ago. Try extended events https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server?view=sql-server-ver16

5
  • That was helpful, thank you. I followed the MS tutorial to get my feet wet. The only thing is that the fields are really limited compared to the profiler, it seems. I don't have any options to see the user that did the event. Here's what I have. There are a few more options but nothing to do with users. imgur.com/FD2MYMs Jul 7, 2023 at 19:03
  • Do you (your account) have permission to see other sessions?
    – SergeyA
    Jul 7, 2023 at 21:59
  • I imagine it does, but I will try to check Jul 10, 2023 at 14:22
  • Look at the picture of Event configuring in example and compare it to your screenshot: the screenshot is missing global fields (action) tab. I think missing permission is a reason why profiler didn't work in the first place
    – SergeyA
    Jul 10, 2023 at 14:26
  • I tried checking my permissions this way: sys.fn_my_permissions. These are the permissions I got, it seems like i should be able to do anything. i.imgur.com/9x1MRel.png Jul 10, 2023 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.