Audit Select statements - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Audit Select statements

Author

Topic

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-27 : 02:48:58

Hi all,

How to audit Select statements in sql server 2005. thanks in advance

Iam a slow walker but i never walk back

svicky9
Posting Yak Master

232 Posts

Posted - 2009-04-27 : 07:31:23

You need to use profiler to log the events and then export them in a table.It may add to lot of overhead.

If the end users are using a procedure something like

create procedure TEST_SELECT
as
select col2 from Table1

then you can modify this procedure something like

SET NO COUNT ON

create procedure TEST_SELECT
as

Declare @SQL nvarchar(max)

Begin

set @SQL = 'select col2 from Table'

Exec(@sql)

Insert into AuditTable(Username,Query) values (@@USERNAME,@SQL)

End

http://www.sqlserver007.com

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-27 : 09:04:58

Hi thanks for reply,

i would love using sql profiler. i have stored the profiler data to a table named newtrace. and then iam creating a trigger for that newtrace table

ALTER TRIGGER tr_Trace ON TraceTrigger
FOR INSERT
AS
DECLARE @INT INT
SELECT CAST(TextData AS VARCHAR(MAX)) FROM INSERTED

GO

when i try to create this trigger i get the error

Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

Iam a slow walker but i never walk back

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources