Find Permission Changes In The Default Trace

Question

Post reply

Find Permission Changes In The Default Trace

Lori B

Ten Centuries

Points: 1149
More actions
October 13, 2015 at 12:04 am

#304171

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

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

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

bsclyde SSChasing Mays Points: 601 More actions · Answer 1

bsclyde

SSChasing Mays

Points: 601

October 13, 2015 at 8:42 am

#1833008

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: 13862 More actions · Answer 2

g.britton

SSChampion

Points: 13862

October 13, 2015 at 2:42 pm

#1833083

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

Gerald Britton, Pluralsight courses

SethT3 SSCommitted Points: 1623 More actions · Answer 3

SethT3

SSCommitted

Points: 1623

October 13, 2015 at 2:45 pm

#1833086

Thank you very much! Well done.

gkramer 23701 Ten Centuries Points: 1364 More actions · Answer 4

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>

<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 ">

<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">'+ @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;">'+ @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;">