Welcome to Sign in | Join | Help
in Search

Auditare Sql Server 2008

Last post 06-14-2011, 9:14 PM by xmldeveloper. 6 replies.
Sort Posts: Previous Next
  •  03-31-2011, 12:55 PM 8675

    Auditare Sql Server 2008

    Salut tuturor
    Am facut o auditare la un server SQL dar in fisierul sqlaudit rezultat la statement nu vad si valoarea parametrilor
    De ex. am SELECT * FROM tabel WHERE cod = @cod
    Cum sa fac sa vad si valoarea parametrilor ?
    As vrea sa fac totul din facilitatea de auditare a SQL Server fara sa folosesc triggere pe tabele ptr INSERT, UPDATE, DELETE etc.
    Multumesc



    Sorin Sandu
  •  04-02-2011, 2:54 PM 8677 in reply to 8675

    Re: Auditare Sql Server 2008

    Puteti reda codul pentru generarea auditului?

    Cristian Andrei Lefter, SQL Server MVP
    MCT, MCSA, MCDBA, MCAD, MCSD .NET,
    MCTS, MCITP - Database Administrator SQL Server 2005
    http://sqlserver.ro
  •  04-04-2011, 12:00 PM 8681 in reply to 8677

    Re: Auditare Sql Server 2008

    E facut cu SSMS dar arata asa
    USE [master]
    GO


    CREATE SERVER AUDIT [My_Security_Audit]
    TO FILE
    (    FILEPATH = N'D:\Audit\'
        ,MAXSIZE = 0 MB
        ,MAX_ROLLOVER_FILES = 2147483647
        ,RESERVE_DISK_SPACE = OFF
    )
    WITH
    (    QUEUE_DELAY = 1000
        ,ON_FAILURE = CONTINUE
        ,AUDIT_GUID = 'f43a9ca2-c7db-421c-9b25-fc178b39aa8f'
    )
    GO


    USE [Database]
    GO

    CREATE DATABASE AUDIT SPECIFICATION [Audit_Date]
    FOR SERVER AUDIT [My_Security_Audit]
    ADD (DELETE ON SCHEMA::[dbo] BY [userul]),
    ADD (EXECUTE ON SCHEMA::[dbo] BY [userul]),
    ADD (INSERT ON SCHEMA::[dbo] BY [userul]),
    ADD (SELECT ON SCHEMA::[dbo] BY [userul]),
    ADD (UPDATE ON SCHEMA::[dbo] BY [userul])
    WITH (STATE = ON)
    GO



    Sorin Sandu
  •  04-12-2011, 3:39 PM 8686 in reply to 8677

    Re: Auditare Sql Server 2008

    Deci se poate sau nu ?

    Sorin Sandu
  •  04-25-2011, 2:04 PM 8695 in reply to 8686

    Re: Auditare Sql Server 2008

    Se poate. Urmatorul cod mie personal imi functioneaza:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    USE [master]
    GO

    CREATE SERVER AUDIT [MySecurityAudit]
    TO FILE
    (  FILEPATH = N'D:\'
      ,MAXSIZE = 0 MB
      ,MAX_ROLLOVER_FILES = 2147483647
      ,RESERVE_DISK_SPACE = OFF
    )
    WITH
    (  QUEUE_DELAY = 1000
      ,ON_FAILURE = CONTINUE
      ,AUDIT_GUID = 'a117a005-c827-4277-bd3a-374243af7b99'
    )
    GO

    1
    2
    3
    4
    5
    6
    7
    8
    USE [DemoDB]
    GO

    CREATE DATABASE AUDIT SPECIFICATION [DB_Audit]
    FOR SERVER AUDIT [MySecurityAudit]
    ADD (EXECUTE ON OBJECT::[dbo].[usp_Test] BY [User1])
    WITH (STATE = ON)
    GO

    Se poate vedea in imagine ca este capturata si valoarea parametrului:








    Cristian Andrei Lefter, SQL Server MVP
    MCT, MCSA, MCDBA, MCAD, MCSD .NET,
    MCTS, MCITP - Database Administrator SQL Server 2005
    http://sqlserver.ro
  •  04-27-2011, 2:23 PM 8697 in reply to 8695

    Re: Auditare Sql Server 2008

    Poti sa-mi spui te rog cum ai facut captura ?
    Se pare ca ceea ce vreau eu este Change Tracking nu audit.
    La insert sau update nu arata valorea parametrilor

    Sorin Sandu
  •  06-14-2011, 9:14 PM 8780 in reply to 8697

    Re: Auditare Sql Server 2008

    Captura a fost facuta cu SnagIT ca program si reflecta rezultatul audit-ului.
    Change Tracking este o functionalitate SQL Server care monitorizeaza modificarea datelor intr-un tabel si nu executia procedurilor stocate.

    Cristian Andrei Lefter, SQL Server MVP
    MCT, MCSA, MCDBA, MCAD, MCSD .NET,
    MCTS, MCITP - Database Administrator SQL Server 2005
    http://sqlserver.ro
View as RSS news feed in XML
Powered by Community Server (Commercial Edition), by Telligent Systems