ALTER TRACE permissions for non-DBAs

would you consider it security appropriate to give this permission to developers.. what may be pros/cons.
LVL 5
25112Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbaSQLCommented:
In my opinion, no, but let me explain.  As you can see in the two links I've pasted below, ALTER TRACE is a server-level permission that allows joe-developer to start, stop or modify traces.  This in itself should be enough for you to stop for a second;  Understand, traces can be very invasive.  

https://msdn.microsoft.com/en-us/library/ms187611(v=sql.110).aspx
https://msdn.microsoft.com/en-us/library/cc293611.aspx
NOTE:  The 2nd is a v2005 reference, but the ALTER TRACE affects are the same. I just believe this one provides more detail regarding the considerations you should make if considering to grant this to anyone other than sysadmin.

Generally, SQL Server Profiler can dramatically affect performance.  In my book, these traces should never be run during a production day, unless completely unavoidable. IF/WHEN it is necessary, it must be done correctly because out of the box, the SQL Server Profiler trace is very invasive.  That is the key point; traces are invasive. The only way to ease the overhead is to enable the correct filters and be very careful about the information that is being collected, and WHEN it is being collected.  IF we need to trace any database activity, we should always use a server side trace.  Profiler traces can kill the database instance.  I've done it myself, and it is easily simulated.

The invasive-ness has always been an issue w/the Profiler, and I'm betting that is probably one of the reasons why the sql profiler trace capture/replay has been deprecated.  Still available, but will be pulled from a future edition.  As you can see here:  
   https://msdn.microsoft.com/en-us/library/ms181091.aspx

Spefically, ALTER TRACE is a server-level permission which gives your developers server-level abilities, without restriction. If joe-developer can get in there at that level, you need to first recognize that this is a security risk. Then you should be comfortable that he/she will only retrieve the information necessary (and authorized), and be sure that he/she is not going to impact production data-flow and user activity.  

If my developers needed to run it, I would enable it in the dev bed.  Set them up w/data and/or activity mimicking production, and let them trace at will in the environment you set up for it, away from production and without the above risks.

I don't mind noting, all of the above comes from a couple of very hard-learned lessons.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
25112Author Commented:
helpful a lot.. thank you
0
dbaSQLCommented:
My pleasure.  Glad to have helped.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.