July 21, 2009 at 10:14 am
Somebody on my SQL Server 2005 Database server runs log truncation command(Backup log woth no_log) sometime after a full backup has been done by the scheduled Maint job.This breaks the backup chain and the next T.Log backup job scheduled fails.This is serious risk to us in case point in time disaster recovery is needed.
I need code/ideas regarding creating a DDL trigger such that anyone running the below sql statement receives an error and the statement is terminated:
Backup log 'dbname' with no_log
Any suggestions regarding this or some other ways in which the objective can be achieved?
July 21, 2009 at 10:24 am
the default trace should have what you are after;
i just did a backup, and confirmed that it appears in the trace.
here's some code for you to test with:
declare @sql varchar(max)
declare @TraceFileName nvarchar(256)
set @TraceFileName = (select path from sys.traces where is_default = 1)
SELECT
trc_evnt.name
,dflt_trc.DatabaseName
,dflt_trc.ApplicationName
,dflt_trc.TextData
,dflt_trc.FileName
,dflt_trc.LoginName
,dflt_trc.StartTime
FROM fn_trace_gettable( @Tracefilename , NULL) AS dflt_trc
INNER JOIN sys.trace_events AS trc_evnt
ON dflt_trc.EventClass = trc_evnt.trace_event_id
WHERE name='Audit Backup/Restore Event'
Lowell
July 21, 2009 at 12:32 pm
Thanks for the suggestion.
This however lets me trace the backup event and the login from which it happens.How does it serve my objective of NOT ALLOWING this event to happen in the first place?
July 21, 2009 at 12:37 pm
...disable the access?.....
As far as I know - you can't hand a DDL trigger off of a BACKUP command.
From the reference on the DDL triggers:
Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME [ ; ] }
::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
::=
assembly_name.class_name.method_name
No BACKUP on the list.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 21, 2009 at 1:25 pm
Disabling access is not an option as there might be 100s of users,anyone of whom might run the backup command.
Heres the solution I have tested and which is more specific to the problem.
Create a new DB role say'db_denybackupoperator' in the concerned database.Under securables for the role,select Database as object type and assign DENY permission under 'Backup Log'.Then add the database user to this role.
Now whenever that user tries to run a Backup log statement he will get the below error:
Msg 262, Level 14, State 1, Line 1
BACKUP LOG permission denied in database 'TEST'.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Thoughts? Any better ideas?
July 21, 2009 at 2:06 pm
Ankur Bajaj (7/21/2009)
Disabling access is not an option as there might be 100s of users,anyone of whom might run the backup command.Heres the solution I have tested and which is more specific to the problem.
Create a new DB role say'db_denybackupoperator' in the concerned database.Under securables for the role,select Database as object type and assign DENY permission under 'Backup Log'.Then add the database user to this role.
Now whenever that user tries to run a Backup log statement he will get the below error:
Msg 262, Level 14, State 1, Line 1
BACKUP LOG permission denied in database 'TEST'.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Thoughts? Any better ideas?
That's what I was getting at (i.e. disabling/denying the access to the backup command, not altogether). Sorry- bad choice of words.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 21, 2009 at 3:25 pm
Ankur Bajaj (7/21/2009)
Disabling access is not an option as there might be 100s of users,anyone of whom might run the backup command.Heres the solution I have tested and which is more specific to the problem.
Create a new DB role say'db_denybackupoperator' in the concerned database.Under securables for the role,select Database as object type and assign DENY permission under 'Backup Log'.Then add the database user to this role.
That assumes that the database user had permission to backup the log in the first place, which they shouldn't have had. It's not going to help against an administrator logging in and running the backup or from a scheduled truncate in a SQL job.
First thing you should consider is identifying where the log truncation command is been issued from, by who and why. Once you know that, then stopping it should be trivial.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2009 at 3:55 pm
This is NOT a technical issue! Lowell gives you a way to identify this culpit.
A database is either LIVE (in which case it needs a resilient backup strategy) or not.
You have a legitimate concern. You are correct to be worried about it because the backup strategy is broken. You have spotted the problem.
Think carefully about how you phrase it and report it to your boss.
You might earn some points...
Tim
.
July 21, 2009 at 4:34 pm
I know who is running these backups.It is small group of application owners who happen to have some basic SQL Admin knowledge.They are not sysadmins,but they do have db_owner permissions in some dbs which is why they can execute backup statements.This is not a 24/7 monitored database which is why when they find that a log file has grown quite large in size,they simply truncate it breaking the chain.
My question was how do I technically prevent them from doing these things without having to talk to them and explain why what they are doing,they shouldnt be doing.And yes a deny privelege overrides whatever permissions he might have even though he may be db_owner,though it cannot override a sysadmin.
What I was interested in was for everyone to look at the larger picture..supposing u get to support a transitioned server where u find that there are hundreds of users who have permission to backup log etc...will u go about identifying and tracing each of these users..or simply disable the functionality for all except sysadmins to execute these backup statements?
July 21, 2009 at 4:44 pm
Why can't you go and speak to them, explain why what they're doing is harmful (lay down the recovery consequences) and ask them to stop. That would be my first step, followed by a revocation of their db_owner privileges if they continue to run those 'backups'.
Get your manager's backing before doing the latter, but if you're the DBA, they are potentially breaking your system and risking data and should not be allowed to do that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2009 at 4:52 pm
Ankur Bajaj (7/21/2009)
supposing u get to support a transitioned server where u find that there are hundreds of users who have permission to backup log etc...will u go about identifying and tracing each of these users..or simply disable the functionality for all except sysadmins to execute these backup statements?
I would apply the principle of least privilege to all the users on the server, removing unnecessary permissions from those who have no right or need to play with backups and ensuring that users have just the permissions that they need to perform their jobs. If the people running the truncation are admins, I would identify them then speak with them and explain why they should not run those commands. If they persist it becomes a disciplinary problem that should be taken up with management.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2009 at 5:22 pm
If this is causing you a problem and you can't get them to stop - you can always set the trace flags to make those options noops instead. Review the article here: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Understanding-the-Transaction-log.aspx
The trace flag information is in the middle, but here is the relevant information from her blog:
So, this means that you don't necessarily need the trace flags. However, I still recommend using the trace flag because it would be better to not break the continuity of the trace flag to begin with! And, in fact, in SQL Server 2005, there are two trace flags: 3231 and 3031. They are both safe and here's how the two differ:
¦Trace Flag 3231 (same as 2000): When set, BACKUP LOG with TRUNCATE_ONLY and BACKUP LOG with NO_LOG do not allow a log backup to run if the database's recovery model is FULL or BULK_LOGGED.
¦Trace Flag 3031 (new in 2005): When set, BACKUP LOG with TRUNCATE_ONLY and BACKUP LOG with NO_LOG run as a CHECKPOINT - regardless of recovery model.
If you turn on those trace flags - then they can issue those commands all they want and nothing will happen. At least, nothing bad 🙂
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 21, 2009 at 6:30 pm
That seems to be another good solution,which will impose server-wide restriction on executing truncate log statements.Thanks for the tip.
July 22, 2009 at 7:05 am
... but as Gail mentioned before, there should be no reason for you not to talk with them and explain why their actions could (and will) cause problems. Ultimately, if something happens, you as the DBA, will be responsible, and your job could be on the line, not theirs.
Yes, you can still set the trace flags, but, the users should be notified. I would take it a step further and also notify your manager on actions taken (talk with user, set trace flag), so he/she is aware of the situation an can help you out if something should go wrong....
July 22, 2009 at 7:17 am
Well,all this communication related stuff is fine and I would be really able to confront them when they come to me saying that off late they havent been able to execute some SQL statements...then I can directly charge them with putting the database at risk by misusing their access n database priveleges.Instead of me going to them,I would want them to come to me,it would give me the upper hand.Then I can explain to them why I have done what I have done and that they shouldnt be doing things on the server without understanding the consequences.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply