Post reply

Trigger On SELECT

  • July 28, 2011 at 2:24 am

    #240745

    Hi,

    I have a table which stores account related sensitive information.

    I want to get an alert whenever that table is queried(issuing SELECT against that table).

    Is it possible to create a trigger for that?

    Thanks

    Thanks,
    Pandeeswaran

  • July 28, 2011 at 2:33 am

    #1359858

    You can trace the execution of a T-SQL statemen using SQL Server Profiler.

    Regards,

    Iulian

  • July 28, 2011 at 4:21 am

    #1359913

    Yes. But is it possible via t-SQL?

    Thanks,
    Pandeeswaran

  • July 28, 2011 at 4:28 am

    #1359917

    pandeesh (7/28/2011)

    Yes. But is it possible via t-SQL?

    No, you can not create a trigger via t-sql for this.

  • July 28, 2011 at 4:53 am

    #1359930

    Read up on SQL audit.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 28, 2011 at 5:03 am

    #1359935

    If the data you have if so sensitive and you want to implement access audit in t-sql (which not the best design idea), you can do the following:

    1. Remove SELECT access to the table from all users

    2. Create stored procedure to retrieve data from the table

    3. Implement custom audit inside of the above stored proc.

    The above is the possible technique which no one, in their right mind, would use 😀

    However I might be wrong... :hehe:

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • July 28, 2011 at 5:14 am

    #1359941

    If you are using SQL SERVER 2008 you can create a SQL SERVER TRACE COLLECTION set it is insert to a table your select queries

    and you can write a triger in that table.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply