SQL 2000 Profiler needs sysadmin server role?

SQL 2000 Profiler needs sysadmin server role?

Post by Steve Smit » Thu, 12 Oct 2000 04:00:00



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.

 
 
 

SQL 2000 Profiler needs sysadmin server role?

Post by Gert E.R. Draper » Thu, 12 Oct 2000 04:00:00


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

 
 
 

SQL 2000 Profiler needs sysadmin server role?

Post by Gert E.R. Draper » Thu, 12 Oct 2000 04:00:00


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

 
 
 

SQL 2000 Profiler needs sysadmin server role?

Post by Darren Gree » Thu, 12 Oct 2000 04:00:00




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.

I have not tested this for 2000, but I recently implemented a role with
permissions to execute xp_trace_* and xp_sqltrace procedures for 7.0,
and it works fine. I assure you I have not given these guys sysadmin
rights, but they *can* use Profiler. In a development environment this
is very important, but can obviously be a big security risk in a
production environment.

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

 
 
 

SQL 2000 Profiler needs sysadmin server role?

Post by Darren Gree » Sun, 15 Oct 2000 04:00:00




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

Indeed, this works a treat and is very handy for our developers.

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.

I tried tracing a specific account which has been granted execute for
sp_trace_*. When connecting via the Profiler GUI, the following
statements  are seen in my second trace (using sysadmin):

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/

 
 
 

1. SQL Profiler & Sysadmin server role

Hi there
A junior developer requires to trace activities using SQL
profiler and requested to be added to the role of sysadmin.
I added his domain WINNT account ,assigned this to the
sysadmin role & away he went.
Previously he was connected using a sql login with no
server role associated but dbo privileges on the user db.

Can anyone enlighten me as to the min privileges required
by SQL profiler ?? (I'm certainly not comfortable with my
solution)

Thks, Hans

2. upadate child in fox 5.0

3. profiler sysadmin role

4. FW: Constraints from a query ?

5. Need resources for SQL Server 2000 Application Roles with ASP.NET apps

6. US-NC-Charlotte ***Mgr. of Bus. Systems Job Posting***

7. db_owner Role (SQL Server 7.0 v/s SQL Server 2000)

8. ESQL/C with MFC

9. sysadmin server role

10. Server Roles - Running sql profiler and not being sa

11. Sql Profiler Sql Server 2000