Auditing and IP addresses

In a database I created an audit table in which, e.g., I insert one record
for each INSERT statement made on another table (via a trigger).
My problem is to recover remote IP address of the connected client which
does the INSERT.
I tried to use DMV sys.dm_exec_connections because it contains a "client_net_address"
field: it was just what was searching for.
But, unfortunately, this DMV requires VIEW SERVER STATE permission, which
is not assigned to any user.
May you tell me how can I solve this problem, avoiding to assign that permission
to everybody?
Thanks all.
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skype:pbsoftsolution
Hi,
this is the only way to get the information. prior to the DMV you had
to retrieve the information from XP_cmdshell:
http://www.sqlserver2005.de/Articles/3/
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de

Auditing and IP addresses

In a database I created an audit table in which, e.g., I insert one record
for each INSERT statement made on another table (via a trigger).
My problem is to recover remote IP address of the connected client which
does the INSERT.
I tried to use DMV sys.dm_exec_connections because it contains a "client_net
_address"
field: it was just what was searching for.
But, unfortunately, this DMV requires VIEW SERVER STATE permission, which
is not assigned to any user.
May you tell me how can I solve this problem, avoiding to assign that permis
sion
to everybody?
Thanks all.
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skype:pbsoftsolutionHi,
this is the only way to get the information. prior to the DMV you had
to retrieve the information from XP_cmdshell:
http://www.sqlserver2005.de/Articles/3/
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--

Auditing and IP addresses

In a database I created an audit table in which, e.g., I insert one record
for each INSERT statement made on another table (via a trigger).
My problem is to recover remote IP address of the connected client which
does the INSERT.
I tried to use DMV sys.dm_exec_connections because it contains a "client_net
_address"
field: it was just what was searching for.
But, unfortunately, this DMV requires VIEW SERVER STATE permission, which
is not assigned to any user.
May you tell me how can I solve this problem, avoiding to assign that permis
sion
to everybody?
Thanks all.
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skype:pbsoftsolutionHi,
this is the only way to get the information. prior to the DMV you had
to retrieve the information from XP_cmdshell:
http://www.sqlserver2005.de/Articles/3/
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||PBsoft (info[REMOVE]@.pbsoft.it) writes:
> In a database I created an audit table in which, e.g., I insert one record
> for each INSERT statement made on another table (via a trigger).
> My problem is to recover remote IP address of the connected client which
> does the INSERT.
> I tried to use DMV sys.dm_exec_connections because it contains a
> "client_net_address"
> field: it was just what was searching for.
> But, unfortunately, this DMV requires VIEW SERVER STATE permission, which
> is not assigned to any user.
> May you tell me how can I solve this problem, avoiding to assign that
> permission to everybody?
Create a certificate and which you sign the trigger with. You need to
have this certificate in master as well. Then create a login from that
certificate, and grant that login the rights.
I have an article on my web site that describes this in a lot more
detail: http://www.sommarskog.se/grantperm.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||try the auditdatabase
auditing tools ( http://www.auditdatabase.com/AuditTools.html ) for
generate audit triggers (FREE) for SQL Server and other DBMS's)
This triggers save the client IP and MAC information
Delia.
Erland Sommarskog ha escrito:
> PBsoft (info[REMOVE]@.pbsoft.it) writes:
> Create a certificate and which you sign the trigger with. You need to
> have this certificate in master as well. Then create a login from that
> certificate, and grant that login the rights.
> I have an article on my web site that describes this in a lot more
> detail: http://www.sommarskog.se/grantperm.html.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx