Very Computer

"In order to run a trace against SQL Server you have to be a member of
sysadmin fixed server role."

I KNOW this isn't true with 7.0 (I can run traces using the same login with
the 7.0 version of Profiler), and I seriously hope it's not true in 2000.

The books online say you need access to the xp_trace* procedures, which
makes sense, but even granting execute rights to these procedures in master
doesn't let me run Profiler.  The only way I seem to be able to run traces
is by actually making the login in question a member of the sysadmin server
role.

Say it ain't so!  I have users who rely on using Profiler as a diagnostic
tool, but making everyone a member of the syadmin role is asking a bit much
just for this functionality?

Is this a new rule in 2000?  Is there any way to get the 2000 version of
profiler to run traces against a 7.0 server that does in fact have
sufficient rights...?

5+e\/e
Steve Smith
Management Reports, Int.

Yes, that is true, I actually do not believe that 7.0 is not sysadmin role
either.
-GertD

You are right in 7.0 you can grant rights to the xp's
From BOL
Permissions Needed to Connect to SQL Server
SQL Server Profiler can connect to Microsoft SQL ServerT using either
Windows NT Authentication Mode or SQL Server Authentication. When Windows NT
Authentication Mode is used to connect to SQL Server, the user account that
is used to run SQL Server Profiler must be granted permission to connect to
SQL Server. The login account must also be granted permissions to execute
SQL Server Profiler extended stored procedures. For more information, see
System Stored Procedures.

In 2000 even when I create a wrapper SP around the SP calls in the engine I
get

Server: Msg 262, Level 14, State 6, Procedure sp_trace_create, Line 22
SP_TRACE_CREATE permission denied in database 'master'.

So it is the server who is not permitting you not to do this, not profiler.

-GertD

Quote:>I have installed SQL 2000 tools, (Profiler is version 8.0.194) and now when
>I try to connect to either a 7.0 or 2000 SQL Server using Profiler, the
>following message comes up:

>"In order to run a trace against SQL Server you have to be a member of
>sysadmin fixed server role."

>I KNOW this isn't true with 7.0 (I can run traces using the same login with
>the 7.0 version of Profiler), and I seriously hope it's not true in 2000.

>The books online say you need access to the xp_trace* procedures, which
>makes sense, but even granting execute rights to these procedures in master
>doesn't let me run Profiler.  The only way I seem to be able to run traces
>is by actually making the login in question a member of the sysadmin server
>role.

>Say it ain't so!  I have users who rely on using Profiler as a diagnostic
>tool, but making everyone a member of the syadmin role is asking a bit much
>just for this functionality?

>Is this a new rule in 2000?  Is there any way to get the 2000 version of
>profiler to run traces against a 7.0 server that does in fact have
>sufficient rights...?

>5+e\/e
>Steve Smith
>Management Reports, Int.

If using 200, try creating a role with execute on the sp_trace_* procs.
--
Darren Green
SQL/DTS - http://www.swynk.com/friends/green/

Quote:>You are right in 7.0 you can grant rights to the xp's

Quote:

>In 2000 even when I create a wrapper SP around the SP calls in the engine I
>get

>Server: Msg 262, Level 14, State 6, Procedure sp_trace_create, Line 22
>SP_TRACE_CREATE permission denied in database 'master'.

>So it is the server who is not permitting you not to do this, not profiler.

set quoted_identifier on use ""

select is_srvrolemember('sysadmin')

The application name is "SQL Profiler", so it would seem that it checks
for role membership itself.

I think this is a bit of a step backwards unfortunately.
--
Darren Green
SQL/DTS - http://www.swynk.com/friends/green/