Find Permission Changes In The Default Trace

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

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

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

    Gerald Britton, Pluralsight courses

  • Thank you very much! Well done.

  • 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.

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

    20150730GKramervs 001Initialsetup

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

    declare @instvarchar(100)

    declare @email_adresnvarchar(100)

    declare @profilevarchar(100)

    declare @report_textvarchar(max)

    declare @subjectvarchar(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

    selectlogdatum, event_time, action_id, database_name, object_name, statement, session_server_principal_name --,database_principal_name

    fromEPD_authentication_audit

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

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

    --andservername = '<<some server from the list>>'

    andservername = @Servername_epd

    andevent_time > getdate()-2

    andstatement not like 'RESTORE VERIFYONLY FROM DISK%'

    andstatement not like 'RESTORE LABELONLY FROM DISK%'

    andstatement not like 'BACKUP LOG % TO DISK%'

    andstatement not like 'SELECT%'

    andstatement not like 'CREATE TABLE%'

    andstatement not like 'CREATE VIEW%'

    andstatement not like 'CREATE FUNCTION %'

    andstatement not like 'DBCC%'

    andstatement 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--ALTERUSER

    (@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 DROPSQL-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 PASSWORDLOGIN / APPLICATION ROLE

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

    (@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 fromEPD_authentication_audit where logdatum < getdate()-180-- must create an un-double script

    delete fromEPD_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),

    logdatumdatetime2,

    runnumint,

    event_timedatetime,

    sequence_numberint,

    action_idvarchar(4),

    succeededbit ,

    permission_bitmaskvarchar(200),

    is_column_permissionbit,

    session_idsmallint,

    server_principal_idint,

    database_principal_idint,

    target_server_principal_idint,

    target_database_principal_idint,

    object_idint,

    class_typevarchar(4),

    session_server_principal_namevarchar(200),

    server_principal_namevarchar(200),

    server_principal_sidvarchar(200),

    database_principal_namevarchar(200),

    target_server_principal_namevarchar(200),

    target_server_principal_sidvarchar(200),

    target_database_principal_namevarchar(400),

    server_instance_namevarchar(400),

    database_namevarchar(400),

    schema_namevarchar(400),

    object_namevarchar(400),

    statementvarchar(8000),

    additional_informationvarchar(8000),

    file_namevarchar(4000),

    audit_file_offsetbigint,

    user_defined_event_idsmallint,

    user_defined_informationvarchar(8000) )

    */

    /*

    AL ALTERUSER

    CR CREATEUSER

    CR CREATESQL LOGIN

    DL DELETEOBJECT

    DR DROPSQL LOGIN

    DR DROPLOGIN

    DR DROPROLE

    DR DROPUSER

    G GRANTLOGIN

    GWG GRANT WITH GRANTLOGIN

    PWC CHANGE PASSWORDLOGIN

    PWC CHANGE PASSWORDAPPLICATION ROLE

    PWCSCHANGE OWN PASSWORDLOGIN

    */

    GO

  • 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. 🙁

  • 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.

  • 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