side effects of giving permissions to run traces

Sam 1,476 Reputation points
2024-07-04T08:23:42.3466667+00:00

Hi All,

I would like to know the downsides of giving permissions to run traces in lower environments (DEV/QA/UAT). Our DB team has only two members: one supports PST hours, and the other supports IST hours. The PST resource is on leave for a month, so I have to cover both time zones as needed.

The issue is that the Dev team used to come to us when they needed to capture traces for slow-running queries or to capture workload data to send to the vendor for recommendations. Since this is a non-prod environment and they are doing it regularly, they are requesting permission to run traces themselves.

Should we grant this permission? If yes, should we limit it to 1 or 2 days and then revoke it?

Regards,

Sam

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,211 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 105.8K Reputation points MVP
    2024-07-04T09:28:58.5033333+00:00

    I can't say what you should or should not do, because it depends on things I don't know or cannot know. And those things are largely non-technical. What are the security policies in your organisation? What sort of data is in that system? That is, how sensitive is it etc.

    Another thing to weigh in is the skill level of the developers. If they understand that running Trace adds overhead, and that certain events adds tons of overhead, and they therefore are restrictive with their traces to what they need to know that certainly helps. And most of all, they need to understand that the simplest way to sink a server with Trace is to use Profiler, and therefore all long-running traces server-side, even better.

    But overlooking these considerations, I would not be two worried over granting ALTER TRACE to these people. Although, I should hasten to add that since I work in a a developer role myself, I am partial. I would feel very handicapped if I cannot run traces.

    Note: you said "trace", therefore I discussed in those terms. But if they in fact want to run extended events rather than trace, the same considerations apply. But the permissions are different.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful