must be sysadmin for Profiler. Workarounds?
-
In SQL 7, I had created a Tracer role in master and granted privs to it for all the procs used by Profiler. For SQL 2000, i was pleased that there are only 5 procs involved now. HOWEVER, Profiler now has a hardcoded check to ensure the user is in 'sysadmin' role before profiling.
This is bad for me and I expect most out there. My developers are not and will not be members of the sysadmin role on our development servers. Now they have to come to me for traces (=unproductive).
The SQL documentation states that you only have to grant rights on these procs to profile, but MS acknowledges that as a bug in the documentation (not the app). SQL allows members of public to create and run jobs, but you have to be part of sysadmin to trace. Go figure!
Anyway, has anyone found a way around this?
Brian Glass
Sr. Database Adminstrator
Bombardier Aerospace -
We're a small company, hasnt been much of an issue. In most cases they also have access to a local copy of SQL to experiment on, in rare cases I'd elevate a senior developer to sysadmin long enough to do it if I didnt have time.
No good ideas for work around. You could just use Profiler against a copy on another server just to generate the script, then run it using the procs only - no UI. QA has a nice quick profile feature, that gets disabled too.
Andy
-
One of our thoughts right now is to create a second instance on our primary development server just for profiling. We refresh our development databases from our production systems each Monday to ensure good data for testing purposes (we are dealing with time-based data). The databases are just too large to get down to the developers workstations, in most cases.
Brian Glass
Sr. Database Adminstrator
Bombardier Aerospace -
Not a bad solution.If you're doing some sort of standard profiling, you could set it up as a job, maybe add a table to configure it.
Andy
-
Hi bglass, I had a long conversation with one of your developers (Chad S) last night on this issue as I know he's more than a bit disgruntled at not being able to trace in SQL 2K.
There are a couple of options I presented to him:
(1) The SQL Agent approach
Create a SQL Server job which looks into a table. When the SQL Server job finds a request for a profile trace along with a start or stop command, it executes it.
The job would then execute a stored procedure which would start or stop the trace as appropriate. The sp_trace_* system stored procedures can be used to build and execute the trace.
The job would have to poll pretty frequently in order to be responsive to a developer needing a trace.
(2) The xp_cmdshell isql approach
Similar to #1, but this one can be started and stopped on demand by the developer without having to rely on the Agent to kick it off. This requires a couple of things.
(a) The proxy account has to be defined and it has to be able to execute isql or osql. This can, however, be an account local to the database server.
(b) A SQL Server login with sysadmin rights.
(c) A stored procedure which calls xp_cmdshell and invokes isql or osql.
(d) a script for the start and stop of the traces, similar to what you would create with the SQL Agent method.
(e) Grant trusted developers the ability to EXECUTE the start and stop stored procedures for the traces.
The login through isql or osql will need sysadmin rights so the obvious choice is use the proxy account. This will give you the ability to start isql or osql using a Trusted Connection, thereby eliminating the need to hard code a password.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
Depending on the detail you need, wouldnt be hard to put together a UI to build the trace. Not going to get the real time display (anyone know how Profiler does that? Never looked), but would make building simpler - only worth doing if you're doing a lot of variations.
Brian, gotta say thats going the extra mile - going directly to the developers related to the post!
Andy
-
Running sp_helptext on any of the trace stored procedures returns a (server internal) message so Microsoft has chosen to hide it well. I suppose it's just as well.
I understand the rationale behind restricting the Profiler to sysadmin roles only, which is the debate I was having with Chad last night. Chad is the same Chad in the dedication to the eBook.
We were in the Air Force together. When I saw the question, I checked the profile of the poster and low and behold, a match!I'm thinking you're right that it wouldn't be too hard to build a similar interface, perhaps using a trace table and making it almost real time, while still controlling what the developer can toggle on and off in the trace. As I told Chad last night, there is so much that Profiler can do that it represents a real security issue, even in development.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
We were in the Air Force together. When I saw the question, I checked the profile of the poster and low and behold, a match!Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply