February 23, 2010 at 11:17 am
I want to grant a programmer with some previalges to evaluate the perofrmance of SQL queires, i already gave showplan access, do i also need to give access to any DMV's or server/database state ?
if view server/database state access is given, does it give acceess to all system views and would there be any risk ?
February 23, 2010 at 11:54 am
If you check in books Online, it lists the permissions needed to query each DMV in the page on that DMV
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 23, 2010 at 12:52 pm
Actually we have an application on which perofrmance is worst and would like to improve it as much as we can and so we are hiring a tuning specialist for a short term to tune that server. I was just wondering what previlages he needs to perform his job effecientely?
From his covnersation he was asking for access to the performance tools like DTA,profiler,perf monitor,DMV's but we dont want to give him admin access? any idea to manage security ?
February 23, 2010 at 1:11 pm
Check in Books online what the minimum permissions are for each of the things he'll need (ask him for a list) and then evaluate how you're going to do it.
profiler needs alter trace, not sysadmin. Perfmon is a windows tool, so that'll require windows permissions, not SQL. Different DMVs have different minimum security, check in Books Online, it's all there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 23, 2010 at 1:32 pm
these are the 4 areas he is looking for at server level
DTA
Profiler
Perf Monitor
DMV's
what are risk involved in giving VIEW SERVER STATE by which he can access all DMV's ?
Also would like to know of any risk in giving above previalges.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply