Script to find which user deleted the user database in SQL Server

Use the below script to get the details of deleted/dropped databases. We can explicitly specify the name of the database which was deleted or get the output for all databases.

use tempdb

go

 

declare @enable int

select top 1 @enable = convert(int,value_in_use) from sys.configurations where name = ‘default trace enabled’

if @enable = 1 –default trace is enabled

begin

declare @d1 datetime;declare @diff int;declare @indx int ;

declare @curr_tracefilename varchar(500);

declare @base_tracefilename varchar(500);

declare @temp_trace table (obj_name nvarchar(256) collate database_default,database_name nvarchar(256) collate database_default,start_time datetime,

              event_class int,event_subclass int,object_type int,server_name nvarchar(256) collate database_default,login_name nvarchar(256) collate database_default,

              application_name nvarchar(256) collate database_default,ddl_operation nvarchar(40) collate database_default);

 

select @curr_tracefilename = path from sys.traces where is_default = 1 ; set @curr_tracefilename = reverse(@curr_tracefilename)

select @indx  = PATINDEX(‘%\%’, @curr_tracefilename); set @curr_tracefilename = reverse(@curr_tracefilename)

set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) – @indx) + ‘\log.trc’;

 

insert into @temp_trace select ObjectName,DatabaseName,StartTime,EventClass,EventSubClass,ObjectType,ServerName,LoginName,ApplicationName,’temp’

from ::fn_trace_gettable( @base_tracefilename, default ) where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID <> 2 

———————————————————————————————————————————-

and DatabaseName = ‘SomeDB’ — <<<======Specify the name of the database here, else comment this line to get details of databases

———————————————————————————————————————————-

update @temp_trace set ddl_operation = ‘CREATE’ where event_class = 46

update @temp_trace set ddl_operation = ‘DROP’ where event_class = 47

update @temp_trace set ddl_operation = ‘ALTER’ where event_class = 164

 

select @d1 = min(start_time) from @temp_trace

set @diff= datediff(hh,@d1,getdate())

set @diff=@diff/24;

 

select  start_time as Event_Time,Database_name,Server_name,Login_name,Application_name,

–SQLInstance,

              DDL_Operation from @temp_trace where object_type not in (21587)

order by start_time  desc

 

endNote:-  The above query is the modified version of query which is executed in the background when we use SQL Server Reports

Level 3 Support

L3 Level Services will be provided by the L3 Support  for Database Management services. All unknown / new errors and problems will be resolved by this L3 Support.

  • Database planning and Design services
  • Design any special application or security requirements.
  • For new software components determine how to deploy and support
  • Design database objects
  • Determine database parameters
  • Troubleshoot installation
  • Performance tuning of database parameters
  • Work closely with Problem Management organization on the remediation of all problems directly or indirectly related to database engineering.
  • Patch Deployment Process Determination
  • Identify vulnerable systems and determine severity
  • Determine Response until patch is available
  • Implement response until patch is available
  • Monitor for Patches and test
  • Identify patch effects like reboots to calculate deployment time
  • Deploy Patch
  • Confirm Patch Efficacy, no adverse effects
  • Recommend Server Placement/ Hardware Migration
  • Disaster recovery solution design, benchmarking, load testing, implementation, configuration, monitoring and troubleshooting, both internal and external.
  • Injection of HA (Alwayson, Clustering etc.,) Services assessment for new deployments/enhancements
  • Work with Vendors when required
  • Perform root cause analysis

Level 2

Level 2 Services will be provided for the Databases receiving L2 as listed above. L2 Services will be provided in accordance with the procedures documented in the SOPs and will include:

  • Incident Management Services

  • Change Management Services

“Incident Management Services” mean the Services to resolve (or break-fix, as applicable). An Incident which has no known resolution will include the following:

The below all comes under L2 Level support Service.

  • Resolve and document Incidents
  • Break-fix and provide workarounds acceptable to the User that reported the Incident which do not require database changes
  • As applicable, escalate incidents requiring immediate attention by using escalation guidelines
  • Maintain Incident information in the Service Management System
  • Carry out daily, weekly and other regular reporting activities for Incidents
  • Maintain documentation for Incidents
  • Update and Maintain Incident information and will responsible for maintaining & updating operation handbook / Runbooks as well.
  • Startups/Shutdowns of database servers
  • Additional Space allocation
  • Object maintenance
  • Job Purging/reruns
  • Initial troubleshooting
  • Cluster ware troubleshooting
  • Set up database auditing
  • Database refresh
  • Standby/Mirror database re-instantiation
  • End User account requests (create, delete, reset password, roles)
  • Elevated privileges requests for existing or new roles
  • Elevated privilege challenge process
  • Non-expiry profile requests
  • Vendor software installation; includes RDBMS and related add-on products
  • Database creation, clones, copies and moves
  • Database upgrades as defined by the Refresh Report, Schema maintenance
  • Patch planning scheduling and installations
  • Database decommissioning
  • Implementing backup solutions
  • Scripts/tool based backup
  • Disaster recovery testing
  • Backup and recovery via various tools(e.g. – TSM, SQL Server Native tools/SQL Server,  Ad-hoc backup and restore)

 

Level 1 Services

Level1 Services will be provided for the Databases. L1 Services will be provided in accordance with the procedures documented in the SOPs and will include the following:

1. Validate, categorize tickets.
2. Monitor all tickets related to the databases in the Service Management System
3. Carry out daily, weekly and other regular reporting activities as defined in the SOPs
4. Act as the central point of contact between Client and Company for tickets
5. Broadcast communications authorized by the Client to Users
6. Where applicable, keep Client informed and notified on the status and progress of      all tickets.
7. Database Instance availability
8. L1 Database monitoring (Query / Transaction/ Alert log / Trace file/Space etc. )
9. Job Monitoring
10. Replication / stand by monitoring
11. Object monitoring
12. Unauthorized / Inactive User Access
13. Backup status monitoring
14. Disk space / Table space utilization
15. Security and Configuration exception alerts
16. Listener Availability
17. Update and Maintain Incident information.

Note : Report downtimes and uncommon system behavior to Client.

 

Service Deliverables

Service Improvement initiatives to be identified and implemented for frequent issues. (E.g. SQL Job optimization, space issues and long running Jobs.)

Long running issues to be identified for Permanent fixes and root cause analysis. Those issues can be candidates for problem investigation.

SQL Migration documentation for In-place and Side by Side Upgrade on all versions

Run books and Work instructions of all processes which was handed over to team during KT phase

Shadow support / Guided phase will be supported by documents created