Results 1 to 7 of 7
  1. #1
    Join Date
    May 2003
    Posts
    369

    Unanswered: security auditing database

    Hi, I need to write some T-SQL scripts to perform a database audit of several SQL Server 2000 databases that tracks all superuser logins and access to tables. I can do this in Oracle but I am lost with MS SQL Server. Can anyone point me in the right direction? Thanks!

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Look up C2 in BOL.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I did not know that...wonder how many people use it?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Few I am sure, and even fewer know how to make it work in a heavily used prod box.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    May 2003
    Posts
    369

    system tables for tracking user accounts

    Guys,

    I think that you missed my point. I need to write some auditing stored procedures and triggers in Transact SQL that will monitor all stale passwords, user roles, privileges as well as changes made to system and user tables during updates, inserts and deletes. Since I already have done this in Oracle and DB2 UDB with PL-SQL triggers and stored procedures and given that Oracle/DB2 have different system tables with regard to security, roles and account privileges I was wondering if anyone knows how to script this for Microsoft SQL Server 2000 databases? Thanks!

    Scott

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hmmmm, I don't know how else to tell you...You can do it by interrogating the system tables of course, except how are you gonna intercept granting of permissions for example? You're gonna bury yourself in INPUTBUFFER and possibly overload the server by sitting on SYSPROCESSES day in and day out (more like every 3 milliseconds). Open Profiler, open BOL, and create a custom trace, then select Script for SQL 2000...What's so difficult?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by mixxalot
    Hi, I need to write some T-SQL scripts to perform a database audit of several SQL Server 2000 databases that tracks all superuser logins and access to tables. I can do this in Oracle but I am lost with MS SQL Server. Can anyone point me in the right direction? Thanks!
    Rdjabarov is correct, what you need is C2 auditing if you want to guarantee complete and correct auditing.

    If you can live with the level of security that you've created using triggers on Oracle/DB2, you can get that using simple queries in MS-SQL.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •