730
Hi,
i urgently need a script that help me know the user who changed the posting setups in my company and the time these changes were made. I will really appreciate your hepl
Thanks
Ali
*This post is locked for comments
Ruth,
You could create a quick script to do this, as follows:
Share
Report
All responses (
Answers (
CREATE TABLE dbo.Audit_SY02300(
audit_ID uniqueidentifier DEFAULT NEWID() PRIMARY KEY,
audit_LOGTYPE varchar(3),
audit_UPSTDTFR smallint,
audit_BCHEXIST smallint,
audit_PTGENLED tinyint,
audit_SERIES smallint,
audit_TRXSOURC char(25),
audit_PSTOGLHW smallint,
audit_ALTRXPST tinyint,
audit_AUTPSTGL tinyint,
audit_VERBATOT tinyint,
audit_VERTRX tinyint,
audit_REQBAPRV tinyint,
audit_PASSWORD char(15),
audit_INCMCINF tinyint,
audit_Use_Account_Settings tinyint,
audit_POSTINDETAIL tinyint,
audit_USERID varchar(30) DEFAULT SUSER_NAME(),
audit_CHANGED datetime DEFAULT GETDATE()
);
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.Audit_SY02300 TO DYNGRP;
GO
CREATE TRIGGER trAudit_SY02300 ON dbo.SY02300 after update as
insert into Audit_SY02300 (
audit_LOGTYPE,
audit_UPSTDTFR,
audit_BCHEXIST,
audit_PTGENLED,
audit_SERIES,
audit_TRXSOURC,
audit_PSTOGLHW,
audit_ALTRXPST,
audit_AUTPSTGL,
audit_VERBATOT,
audit_VERTRX,
audit_REQBAPRV,
audit_PASSWORD,
audit_INCMCINF,
audit_Use_Account_Settings,
audit_POSTINDETAIL)
SELECT 'OLD',
UPSTDTFR,
BCHEXIST,
PTGENLED,
SERIES,
TRXSOURC,
PSTOGLHW,
ALTRXPST,
AUTPSTGL,
VERBATOT,
VERTRX,
REQBAPRV,
[PASSWORD],
INCMCINF,
Use_Account_Settings,
POSTINDETAIL
FROM deleted del;
insert into Audit_SY02300 (
audit_LOGTYPE,
audit_UPSTDTFR,
audit_BCHEXIST,
audit_PTGENLED,
audit_SERIES,
audit_TRXSOURC,
audit_PSTOGLHW,
audit_ALTRXPST,
audit_AUTPSTGL,
audit_VERBATOT,
audit_VERTRX,
audit_REQBAPRV,
audit_PASSWORD,
audit_INCMCINF,
audit_Use_Account_Settings,
audit_POSTINDETAIL)
SELECT 'NEW',
UPSTDTFR,
BCHEXIST,
PTGENLED,
SERIES,
TRXSOURC,
PSTOGLHW,
ALTRXPST,
AUTPSTGL,
VERBATOT,
VERTRX,
REQBAPRV,
[PASSWORD],
INCMCINF,
Use_Account_Settings,
POSTINDETAIL
FROM inserted ins;
GO
Note that there's also posting settings for the reports stored in the SY02200 table. You can adapt the above outline to do something similar.
Hi Ruth
There are no fields in the table for posting settings that records when changes are made by date or by last user.
If you do want to track changes like this you will need to buy FastPath's audit trails or Rockton software also has an auditing program available which I can't recall the name right now.
These software products allow you to audit different functions such as user who created, user who modified and dates changes made. You can activate them to record this type of data even though the GP table does not contain these values.
#1
Almas Mahfooz
3
User Group Leader
