SQL Server Performance

Row Access level Auditing in sqlserver 2005

Discussion in 'SQL Server 2005 General Developer Questions' started by muraliprofession, Apr 8, 2010.

  1. muraliprofession New Member

    Hi all,
    Basically I will enter the login and password from the application and in turn it will check for the table in SQLServer 2005 of that particular database, by checking whether the given user name is exists in that table, if it is available it will allow to login into the application.
    Here I wish to make a audit whenever a record is being accessed from that table. Is there any possibility to check whether the row has been accessed or not. Or given me some other options how to audit?
    Thanks All.
    Regards,
    Muralikumar.
  2. Adriaan New Member

    I'd suggest setting up permissions the normal way - grant database access only to specific logins or specific Windows groups, create specific database roles, assign permissions only to those roles, and assign logins only to those specific roles/Windows groups.
    Application then only has to try to logon - if login has no permissions then the logon attempt will simply fail.
  3. muraliprofession New Member

    Thanks for your reply Adriaan.. But here I want to audit who has access that particular table at a particular period of time. Is there any way to check it. Thanks...
  4. Adriaan New Member

    There is no such thing as a "select trigger", so the easiest alternative is by giving access only through stored procedures - just include code to log the fact that the user is accessing the data.
  5. satya Moderator

Share This Page