Re: Extended Stored Procedure Permissions Problem

From: Jasper Smith (jasper_smith9_at_hotmail.com)
Date: 03/15/04

  • Next message: Tim Richardson: "Re: Extended Stored Procedure Permissions Problem" 
    Date: Mon, 15 Mar 2004 21:10:57 -0000

    In SQL2000 you have to be a sysadmin to run trace procs. This is a change
    from SQL7 where you could grant exec on the extended stored procedures 

    -- 
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tim Richardson" <anonymous@discussions.microsoft.com> wrote in message
news:dce201c40ad0$2f8e3260$a101280a@phx.gbl...
> I have devloped a .NET windows service to manage
> server-side traces for an ongoing database auditing effort
> here at work.  The service creates traces, rolls them over
> periodically, and loads the trace file contents into a
> reporting instance.  This service logs into each audited
> instance using its a domain user account.  The service must
> call the sp_trace_create, sp_setstatus, sp_setevent, and
> sp_setfilter extented stored procedures.  Addtionally, it
> must access the fn_trace_getinfo and fn_trace_gettable
> functions.
>
> The service works very well when the domain account is a
> member of the sysadmin role in an audited instances.  The
> service fails to work when it is just a regular db user
> with EXECUTE permissions on the above stored procedures.
> The service also does not work when configured as a dbo in
> the master database of the audited instance.
>
> I am guessing that this issue arises from the trace
> procedures being extended stored procedures and perhaps
> there is some underlying OS permission issue.  Can anyone
> offer some guidance that can help me avoid running this
> service as a sysadmin?
>
> Thanks in advance,
> Tim
  • Next message: Tim Richardson: "Re: Extended Stored Procedure Permissions Problem"

    Relevant Pages

    • Re: Extended Stored Procedure Permissions Problem
      ... >In SQL2000 you have to be a sysadmin to run trace procs. ... >> member of the sysadmin role in an audited instances. ... >> with EXECUTE permissions on the above stored procedures. ...
      (microsoft.public.sqlserver.security)
    • Re: Audit Trail on SQL Server
      ... For number 2, triggers will ultimately be your answer, but you're looking at ... a lot of data to capture and code to maintain. ... >> What kind of stored procedures could made this job, ... >> The AudtiTrail table would trace the table and field in which changes ...
      (microsoft.public.sqlserver.programming)
    • Re: pssdiag
      ... I don't think I have ever seen a situation in which trace blocked something. ... This is the method that I used that resulted in blocking during the trace. ... > blocks whe I run the script. ... > Stored Procedures that will be deprecated we just want to ensure that> they ...
      (microsoft.public.sqlserver.tools)
    • Re: Audit Trail on SQL Server
      ... you can use profiler or perhaps log explorer from www.lumigent.com ... You could roll your own by querying the information_schema tables too. ... I need to trace every modification made on a database: ... > What kind of stored procedures could made this job, ...
      (microsoft.public.sqlserver.programming)
    • Re: Profiler: Seven words you can never use in a script?
      ... you have to be a sysadmin to define and execute a trace in SQL Server ... this is a security measure within Profiler. ... -- 'encryption' was found in the text of this event. ...
      (microsoft.public.sqlserver.tools)