October 13, 2015 at 12:04 am
Comments posted to this topic are about the item Find Permission Changes In The Default Trace
October 13, 2015 at 8:42 am
This was a great post and help! So many times I wish I'd had this in the past. Well done and thank you!
October 13, 2015 at 2:42 pm
Why did the developer have security permissions in the first place?
Gerald Britton, Pluralsight courses
October 13, 2015 at 2:45 pm
Thank you very much! Well done.
October 14, 2015 at 4:26 am
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
October 14, 2015 at 6:42 am
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. 🙁
October 16, 2015 at 1:04 pm
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.
October 21, 2015 at 7:31 am
Nice work, thanks.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply