Find who dropped a table or column or view
-
Hi all, There is a bad game going on here that there was a column in the table that's dropped. Can anybody please help me in finding who has dropped that column. Anything like that like table, column or a view I want to check who dropped it. I am also googling, if anybody can help it would be a great help, any link, suggestion or code snippet would be greatly helpful. Thanks in advance buddy.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Hi all, There is a bad game going on here that there was a column in the table that's dropped. Can anybody please help me in finding who has dropped that column. Anything like that like table, column or a view I want to check who dropped it. I am also googling, if anybody can help it would be a great help, any link, suggestion or code snippet would be greatly helpful. Thanks in advance buddy.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
Profiler might help. If you are not tracking it this is a problem. In the future you might look at setting up SQL Sever Profiler or using a third party (we use Idera Compliance Manager).
Mongo: Mongo only pawn... in game of life.
-
Hi all, There is a bad game going on here that there was a column in the table that's dropped. Can anybody please help me in finding who has dropped that column. Anything like that like table, column or a view I want to check who dropped it. I am also googling, if anybody can help it would be a great help, any link, suggestion or code snippet would be greatly helpful. Thanks in advance buddy.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
Try
SELECT SUSER_SNAME([Transaction SID])
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'e.g.
USE [TestDB]
GO--Create table tblTestEmployee
CREATE TABLE [dbo].[tblTestEmployee]
(
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [varchar](100) NOT NULL,
[Address] [varchar](100) NOT NULL
)--Populate some records to the tblTestEmployee
INSERT INTO [dbo].[tblTestEmployee]
VALUES('Emp1','Address1'),('Emp2','Address2'),('Emp3','Address3'),('Emp4','Address4')--Drop the column Address
ALTER TABLE [dbo].[tblTestEmployee] DROP COLUMN [Address]--Find who has done that
GO
SELECT SUSER_SNAME([Transaction SID])
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'
GOResult sa You can also have a look at Audit SQL Server database and see who deleted a column value Hope this helps
-
Try
SELECT SUSER_SNAME([Transaction SID])
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'e.g.
USE [TestDB]
GO--Create table tblTestEmployee
CREATE TABLE [dbo].[tblTestEmployee]
(
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [varchar](100) NOT NULL,
[Address] [varchar](100) NOT NULL
)--Populate some records to the tblTestEmployee
INSERT INTO [dbo].[tblTestEmployee]
VALUES('Emp1','Address1'),('Emp2','Address2'),('Emp3','Address3'),('Emp4','Address4')--Drop the column Address
ALTER TABLE [dbo].[tblTestEmployee] DROP COLUMN [Address]--Find who has done that
GO
SELECT SUSER_SNAME([Transaction SID])
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'
GOResult sa You can also have a look at Audit SQL Server database and see who deleted a column value Hope this helps
Have you used the Apex auditor app and if so was there a noticeable performance hit. Useful bits of info thanks...
Never underestimate the power of human stupidity RAH
-
Hi all, There is a bad game going on here that there was a column in the table that's dropped. Can anybody please help me in finding who has dropped that column. Anything like that like table, column or a view I want to check who dropped it. I am also googling, if anybody can help it would be a great help, any link, suggestion or code snippet would be greatly helpful. Thanks in advance buddy.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
No, these can only be found if you are logging anything in your system. Like, which account initiated which request, which request made what change and what time it was etc. These type of logged data can help in understanding what went wrong (if the actions were wrong) and who made a change. If there is no log, then stop searching any further because there ain't any. But this one loss would teach you to create a logging system in your environment. The system would log the changes being made, it would store the user's ID, time of change and what change was made. In my opinion, you should also keep a backup of your databases so that if (under any case) a table is lost or data is not integrated anymore, you can revert it back to a previous version.
The shit I complain about It's like there ain't a cloud in the sky and it's raining out - Eminem ~! Firewall !~