Find Permission Changes In The Default Trace

  • Lori B

    Ten Centuries

    Points: 1149

    Comments posted to this topic are about the item Find Permission Changes In The Default Trace

  • bsclyde

    Mr or Mrs. 500

    Points: 570

    This was a great post and help! So many times I wish I'd had this in the past. Well done and thank you!

  • g.britton

    SSChampion

    Points: 13685

    Why did the developer have security permissions in the first place?

    Gerald Britton, MCSE-DP, MVPToronto PASS Chapter[/url]

  • SethT3

    SSCommitted

    Points: 1623

    Thank you very much! Well done.

  • gkramer 23701

    Ten Centuries

    Points: 1351

    To ALL,

    I think more people must be aware about the possibility using a SQL audit.

    I made a first setup some months ago which I like to share......

    Regards,

    Guus Kramer

    The Netherlands

    ---------------------------------------------------------------------------------------------------------------------------------------------

    CREATE procedure [dbo].[DBA_SQL_Authorisation_Audit]

    as

    begin

    /*-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    This script is gathering information from some servers which are vulnerable for authentiaction changes

    -- servers : <<.... your selection/description here.....>>

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    This script is gathering info through a direct read using OPEN_ROWSET to some server which are in constant update (and suspected actions)

    It stores the gathered data in a local table which will be kept for 180 days (as history).

    Report will be done using HTML and e-mailing.

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    20150730 GKramer vs 001 Initialsetup

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

    declare @inst varchar(100)

    declare @email_adres nvarchar(100)

    declare @profile varchar(100)

    declare @report_text varchar(max)

    declare @subject varchar(300)

    set @inst = 'server or loction you run this script'

    set @email_adres = 'someone@somewhere.com'

    set @profile = 'DBMAIL profile'

    set @subject = @inst +' DETAIL REPORT on the EPD SQL Servers authentication (Netwerk Version)'

    ----------------------------------------------------------------------------------------

    set nocount on

    /*---------------------------------------------------------------------------------------------------

    -- Retrieveing the servers which are

    ---------------------------------------------------------------------------------------------------*/

    Declare @runnum int

    set @runnum = (select isnull(max(runnum)+1,1) from EPD_authentication_audit)

    /*---------------------------------------------------------------------------------------------------

    -- Retrieveing the servers which are

    ---------------------------------------------------------------------------------------------------*/

    --<<SQLServer\instance>> (2012)

    insert into EPD_authentication_audit

    select '<<SQLServer\instance>>', getdate(), @runnum ,

    * FROM OPENROWSET('SQLNCLI10','server=<<SQLServer\instance>>;trusted_connection=yes',

    'set fmtonly off SELECT * FROM fn_get_audit_file(''\\<shared_location>>\AUDITLOG_SQL\*'', default, default)')

    -- duplicate the above script with other servernames

    --***** OTHER SERVERS *****************************************************************************

    -- <<SQLServer\instance>> (2014 - two more columns as 2012)

    insert into EPD_authentication_audit

    select '<<SQLServer\instance>>', getdate(), @runnum ,

    * FROM OPENROWSET('SQLNCLI10','server= <<SQLServer\instance>>;trusted_connection=yes',

    'set fmtonly off SELECT

    [event_time],[sequence_number],[action_id],[succeeded],[permission_bitmask],[is_column_permission],[session_id] ,

    [server_principal_id] ,[database_principal_id] ,[target_server_principal_id] ,[target_database_principal_id] ,[object_id] ,

    [class_type] ,[session_server_principal_name] , [server_principal_name],[server_principal_sid] ,

    [database_principal_name] ,[target_server_principal_name] , [target_server_principal_sid] , [target_database_principal_name] ,[server_instance_name] ,

    [database_name] ,[schema_name] ,[object_name] , [statement] ,[additional_information] , [file_name],

    [audit_file_offset],[user_defined_event_id] ,[user_defined_information]

    FROM fn_get_audit_file(''\\<shared_location>>\AUDITLOG_SQL\*'', default, default)')

    /**************************************************************************************************

    REPORTING SECTION

    **************************************************************************************************/

    set @report_text =

    '<style type="text/css">.style1 {color: #FF0000;}</style>

    <em>

    <strong>AUDIT on EPD SERVERS regarding security issues (eg. alter logins and grants on objects)</strong>

    For information on "ACTION_ID" use ; select * from sys.dm_audit_actions order by 1

    <strong>Some important abbriviations (with no statement);</strong>

    AUSC = AUDIT SESSION CHANGED

    <strong>Statements wich have been excluded to this report (but recorded and saved in the "EPD_authentication_audit" table)</strong>

    RESTORE VERIFYONLY FROM DISK

    RESTORE LABELONLY FROM DISK

    BACKUP LOG % TO DISK

    SELECT

    CREATE TABLE / CREATE VIEW / CREATE FUNCTION

    DBCC

    OPEN SYMMETRIC KEY

    </em>

    '

    /*---------------------------------------------------------------------------------------------------------------

    INCLUDED SERVER SECTION

    ----------------------------------------------------------------------------------------------------------------*/

    select distinct(servername) into #SER_INCL from EPD_authentication_audit order by 1

    set @report_text = @report_text +

    '

    <strong><em>Severs included in the report (distint servername from tabel)</em></strong>

    <table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse; border-color: #111111; font-family: Calibri; font-size: 11">

    <tr style="background-color: #408080; color: #FFFFFF; width: 1500px">

    <th style="padding-left: 10; padding-right: 10; width: 150px">Servername Name</th>

    </tr>'

    select @report_text = @report_text +

    '<tr>

    <td style="padding-left: 10; padding-right: 10;">' + servername + '</td>

    </tr>'

    from #SER_INCL

    set @report_text = @report_text + '</table>

    '

    drop table #SER_INCL

    /*---------------------------------------------------------------------------------------------------------------

    CURSOR SECTION

    ----------------------------------------------------------------------------------------------------------------*/

    -- creating the main table HEADER

    set @report_text = @report_text +

    '<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse; border-color: #111111; font-family: Calibri; font-size:11 ">

    <tr style="background-color: #408080; color: #FFFFFF;">

    <th style="padding-left: 10; padding-right: 10;">SERVER</th>

    <th style="padding-left: 10; padding-right: 10;">LOGDATA</th>

    <th style="padding-left: 10; padding-right: 10;">EVENT_TIME</th>

    <th style="padding-left: 10; padding-right: 10;">ACTION_ID</th>

    <th style="padding-left: 10; padding-right: 10;">DATABASENAME</th>

    <th style="padding-left: 10; padding-right: 10;">PRINCIPAL</th>

    <th style="padding-left: 10; padding-right: 10;">OBJECTNAME</th>

    <th style="padding-left: 10; padding-right: 10;">STATEMENT</th>

    </tr>'

    declare @Servername_epd varchar(200)

    declare @logdatum varchar(40), @event_time varchar(40), @action_id varchar(10), @database_name varchar(200), @object_name varchar(200), @statement varchar(max) , @DPN varchar(200)

    declare EPD_AUD_DS cursor for

    select distinct servername from EPD_authentication_audit order by 1

    open EPD_AUD_DS

    fetch next from EPD_AUD_DS into @Servername_epd

    while @@fetch_status = 0

    begin

    ------------------------------------------------------------------------------------------------------------------

    print @Servername_epd

    declare EPD_AUD cursor for

    select logdatum, event_time, action_id, database_name, object_name, statement, session_server_principal_name --,database_principal_name

    from EPD_authentication_audit

    where runnum = (select max(runnum) from EPD_authentication_audit where servername = @Servername_epd )

    --where runnum = (select max(runnum) from EPD_authentication_audit where servername = '<<some server from the list>>' )

    --and servername = '<<some server from the list>>'

    and servername = @Servername_epd

    and event_time > getdate()-2

    and statement not like 'RESTORE VERIFYONLY FROM DISK%'

    and statement not like 'RESTORE LABELONLY FROM DISK%'

    and statement not like 'BACKUP LOG % TO DISK%'

    and statement not like 'SELECT%'

    and statement not like 'CREATE TABLE%'

    and statement not like 'CREATE VIEW%'

    and statement not like 'CREATE FUNCTION %'

    and statement not like 'DBCC%'

    and statement not like 'OPEN SYMMETRIC KEY%' --Decrypts a symmetric key and makes it available for use.

    open EPD_AUD

    fetch next from EPD_AUD into @logdatum, @event_time, @action_id, @database_name, @object_name, @statement, @DPN

    while @@fetch_status = 0

    begin

    if (@action_id = 'AL' and lower(@statement) like '%alter%' ) or --ALTER USER

    (@action_id = 'CR' and lower(@statement) like '%create%' ) or --CREATE USER / CREATE SQL LOGIN

    (@action_id = 'DL' and lower(@statement) like '%delete%' ) or --DELETE OBJECT

    (@action_id = 'DR' and lower(@statement) like '%drop%' ) or --DROP DROP SQL-LOGIN / LOGIN / ROLE / USER

    (@action_id = 'G' and lower(@statement) like '%grant%' ) or --GRANT LOGIN / GRANT LOGIN WITH GRANT

    (@action_id = 'GWG' and lower(@statement) like '%grant with%' ) or --GRANT LOGIN WITH GRANT

    (@action_id = 'PWC' and lower(@statement) like '%password%' ) or --CHANGE PASSWORD LOGIN / APPLICATION ROLE

    (@action_id = 'PWCS'and lower(@statement) like '%own password%' ) or --CHANGE OWN PASSWORD LOGIN

    (@action_id = 'APRL'and lower(@statement) like '%alter role%' ) or --ADD MEMBER (SERVER) ROLE

    (@action_id = 'USAF') --CHANGE USERS LOGIN AUTO USER

    begin

    select @report_text = @report_text +

    '<tr>

    <td style="padding-left: 10; padding-right: 10;">'+ @Servername_epd +'</td>

    <td style="padding-left: 10; padding-right: 10;">'+ @logdatum +'</td>

    <td style="padding-left: 10; padding-right: 10;">'+ @event_time +'</td>

    <td style="padding-left: 10; padding-right: 10;"class="style1">'+ @action_id +'</td>

    <td style="padding-left: 10; padding-right: 10;"class="style1">'+ @database_name +'</td>

    <td style="padding-left: 10; padding-right: 10;"class="style1"><strong>'+ @DPN +'</strong></td>

    <td style="padding-left: 10; padding-right: 10;"class="style1">'+ @object_name +'</td>

    <td style="padding-left: 10; padding-right: 10;"class="style1"><strong>'+ substring(@statement ,1 ,160) +'</strong></td>

    </tr>'

    end

    else

    begin

    select @report_text = @report_text +

    '<tr>

    <td style="padding-left: 10; padding-right: 10;">'+ @Servername_epd +'</td>

    <td style="padding-left: 10; padding-right: 10;">'+ @logdatum +'</td>

    <td style="padding-left: 10; padding-right: 10;">'+ @event_time +'</td>

    <td style="padding-left: 10; padding-right: 10;">'+ @action_id +'</td>

    <td style="padding-left: 10; padding-right: 10;">'+ @database_name +'</td>

    <td style="padding-left: 10; padding-right: 10;">'+ @DPN +'</td>

    <td style="padding-left: 10; padding-right: 10;">'+ @object_name +'</td>

    <td style="padding-left: 10; padding-right: 10;">'+ substring(@statement ,1 ,160) +'</td>

    </tr>'

    end

    fetch next from EPD_AUD into @logdatum, @event_time, @action_id, @database_name, @object_name, @statement, @DPN

    end

    CLOSE EPD_AUD

    DEALLOCATE EPD_AUD

    ------------------------------------------------------------------------------------------------------------------

    ----CREATE AN EMPTY LINE IN THE TABLE

    --select @report_text = @report_text +

    -- '<tr>

    -- <td style="padding-left: 10; padding-right: 10;"></td>

    -- </tr>'

    -- CREATE a header for each section

    select @report_text = @report_text +

    '<tr style="background-color: #408080; color: #FFFFFF;">

    <th style="padding-left: 10; padding-right: 10;">SERVER</th>

    <th style="padding-left: 10; padding-right: 10;">LOGDATA</th>

    <th style="padding-left: 10; padding-right: 10;">EVENT_TIME</th>

    <th style="padding-left: 10; padding-right: 10;">ACTION_ID</th>

    <th style="padding-left: 10; padding-right: 10;">DATABASENAME</th>

    <th style="padding-left: 10; padding-right: 10;">PRINCIPAL</th>

    <th style="padding-left: 10; padding-right: 10;">OBJECTNAME</th>

    <th style="padding-left: 10; padding-right: 10;">STATEMENT</th>

    </tr>'

    ------------------------------------------------------------------------------------------------------------------

    fetch next from EPD_AUD_DS into @Servername_epd

    end

    CLOSE EPD_AUD_DS

    DEALLOCATE EPD_AUD_DS

    set @report_text = @report_text + '</table>

    '

    /**************************************************************************************************

    E-MAIL SECTION

    **************************************************************************************************/

    --print @report_text

    exec msdb.dbo.sp_send_dbmail

    @profile_name = @profile,

    @recipients = @email_adres,

    @subject = @subject,

    @body = @report_text,

    @body_format = 'HTML'

    /**************************************************************************************************

    CLEANUP SECTION

    **************************************************************************************************/

    --delete from EPD_authentication_audit where logdatum < getdate()-180 -- must create an un-double script

    delete from EPD_authentication_audit where runnum < (@runnum -16) -- keep 2 weeks and 1 day only (due to the growsize of the table

    /*************************************************************************************************/

    end

    /*

    -- select * from sys.dm_audit_actions

    drop table EPD_authentication_audit

    delete from EPD_authentication_audit

    create table EPD_authentication_audit (

    servername varchar(200),

    logdatum datetime2,

    runnum int,

    event_time datetime,

    sequence_number int,

    action_id varchar(4),

    succeeded bit ,

    permission_bitmask varchar(200),

    is_column_permission bit,

    session_id smallint,

    server_principal_id int,

    database_principal_id int,

    target_server_principal_id int,

    target_database_principal_id int,

    object_id int,

    class_type varchar(4),

    session_server_principal_name varchar(200),

    server_principal_name varchar(200),

    server_principal_sid varchar(200),

    database_principal_name varchar(200),

    target_server_principal_name varchar(200),

    target_server_principal_sid varchar(200),

    target_database_principal_name varchar(400),

    server_instance_name varchar(400),

    database_name varchar(400),

    schema_name varchar(400),

    object_name varchar(400),

    statement varchar(8000),

    additional_information varchar(8000),

    file_name varchar(4000),

    audit_file_offset bigint,

    user_defined_event_id smallint,

    user_defined_information varchar(8000) )

    */

    /*

    AL ALTER USER

    CR CREATE USER

    CR CREATE SQL LOGIN

    DL DELETE OBJECT

    DR DROP SQL LOGIN

    DR DROP LOGIN

    DR DROP ROLE

    DR DROP USER

    G GRANT LOGIN

    GWG GRANT WITH GRANT LOGIN

    PWC CHANGE PASSWORD LOGIN

    PWC CHANGE PASSWORD APPLICATION ROLE

    PWCS CHANGE OWN PASSWORD LOGIN

    */

    GO

  • Lori B

    Ten Centuries

    Points: 1149

    Gerald Britton - That is the first thing that I always ask my clients. What I usually find is that many small and even some larger shops are simply used to giving developers unfettered access to production without considering the implications of doing so. Or they realize the dangers but think that surely everyone will be very careful and not make any mistakes. For those places, I generally advise that they take frequent backups and to be prepared to restore to fix mistakes when they will invariably happen. 🙁

  • davidturpin

    SSC Veteran

    Points: 216

    Thanks Lori,

    I had the need for this functionality recently. I tried using AUDIT as well as sql TRACE, but I was being too selective in my choice of the event that fired, so I missed what I was looking for.

    The inclusion of so many events takes away the guess work.

    The DEFAULT trace is a gold mine; like resource governor, way underutilized.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Nice work, thanks.

Viewing 8 posts - 1 through 8 (of 8 total)

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