Extended Stored Procedure Permissions Problem

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,
TimIn 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|||Thanks, Jasper.

>--Original Message--
>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...
>
>.
>

Exporting User/Role Permissions

I am not a DBA so please be gentle...

I am trying to export all of the user and role permissions out of several databases for auditing purposes. I see the Users and Roles listed under the Security tree view when I log into the database, but I do not see an option to export or query the permissions. In addition, we do not have any tables that reference user permissions in our databases. So, how would one go about exporting or querying this information?

I've seen similar topics where they recommend querying sys tables to gather the info, but I don't see those tables either. Any help would be greatly appreciated.

All my thanks!

- Isaac

Edit: I should add in that I am connecting to 7 and 2k DBs using 2k5 SMS. Not sure if that makes a difference...

You can query the tables, such as sys.server_permissions, sys.server_principals, sys.database_principals, sys.database_permissions, to display the permissions of database user and role and logins. E.g., if you want to look at the permission of user Bob, you can query as follows

select * from sys.database_permissions where grantee_principal_id =(select principal_id from sys.database_principals where name='Bob')