Problems with System Stored Procedures Permissions   

Hello, 
 I have a problem with Server Traces. Actually I'm running some different 
audit traces on SQL2K, that I can access with SQL Profiler or using System 
UDF like "fn_trace_getinfo" and "fn_trace_gettable".
The problem is that I need to access to the audit trace files with a 
different user, who is not in sysadmin role. So, I want this is user to be 
capable to execute that system UDF.
I grant select permissions on "fn_trace_getinfo" to the user,but when I try 
to execute a simple query like 
"select * from :: fn_trace_getinfo (default)", I receive a message like 
this: 

Server: Msg 262, Level 14, State 12, Procedure fn_trace_getinfo, Line 10
FN_TRACE_GETINFO permission denied in database 'master'.

Despite this message I have the permissions I need on master. I've been 
reading out in the web and books, and all them say that a member of fix 
sysadmin role can execute the UDF. But I wonder if there is a way to give 
permissions to another users.

Can anyone help me please?

Thanks in advance.

date: Thu, 9 Feb 2006 05:41:02 -0600   author:   Victor

Re: Problems with System Stored Procedures Permissions   

Victor wrote:
> Hello,
> I have a problem with Server Traces. Actually I'm running some
> different audit traces on SQL2K, that I can access with SQL Profiler
> or using System UDF like "fn_trace_getinfo" and "fn_trace_gettable".
> The problem is that I need to access to the audit trace files with a
> different user, who is not in sysadmin role. So, I want this is user
> to be capable to execute that system UDF.
> I grant select permissions on "fn_trace_getinfo" to the user,but when
> I try to execute a simple query like
> "select * from :: fn_trace_getinfo (default)", I receive a message
> like this:
>
> Server: Msg 262, Level 14, State 12, Procedure fn_trace_getinfo, Line
> 10 FN_TRACE_GETINFO permission denied in database 'master'.
>
> Despite this message I have the permissions I need on master. I've
> been reading out in the web and books, and all them say that a member
> of fix sysadmin role can execute the UDF. But I wonder if there is a
> way to give permissions to another users.
>
> Can anyone help me please?
>
> Thanks in advance.

Running traces in SQL 2000 is limited to system administrators. SQL 2005 
allows you to grant trace rights using the Alter Trace grant.

-- 
David Gugick - SQL Server MVP
Quest Software

date: Sun, 12 Feb 2006 16:04:22 -0500   author:   David Gugick