On Sat, 19 Jul 2008 10:24:36 -0400, Noor wrote:

Hi Noor,

Three steps:

1) Gather an overview of all information required. Many people believe
this is the hardest part. It's not. It is hard, mind you, but only if
you believe that you must absolutely get it 100% right at the first go.
You don't. Just do your stinking best to get as close to 100% as
possible and prepare to add extra columns, and even tables, at a later
stage.

2) Find all functional dependencies. This is what actually is the
hardest part. Because the FDs are so simple for 95% of all cases that
one is often tempted to believe it's simple for the remaining 5% as
well. Unfortunately, errors made in this part are often harder to
correct than errors made in step 1.

3) Normalize. This is very simple once the FDs are known. Just follow
the rules up to at least 3NF (but preferably alll the way down to 5NF)
and you're set.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

when you are designing a auditing table , you have to carefully consider the
events which you wanted to store in database. saving hundred 's of event is
nothing but the waste of space . if you are using sql server 2005 sp2 then
you can use ddl auditing. In SQL
2005, a trigger can be created in order to perform any action upon a DDL
statement.

first, create a table to store event log in database

CREATE TABLE admin.logs (
LogID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
Event_Data XML)

create trigger ServerWideLoginLogs
on all server
with execute as self
for LOGON
as begin
DECLARE @event XML
SET @event = eventdata()
INSERT INTO Admin_Log.admin.logs
(EventTime,EventType,LoginName,HostName,AppName,Ev ent_Data)
VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS
VARCHAR(64)) AS DATETIME),

CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS
VARCHAR(100)),

APP_NAME(),

@event)

end

GO

The Event_Data field is populated by the EVENTDATA() function from our
INSERT statement in our trigger. In this case, the eventdata() function
returns XML data related to the connection, including the date and time of
the login, the server name, the user ID, and the machine system ID.

this is simple auditing trigger i am sure it will help you . you can change
it according to your needs it capture the enough events to track down fault.

manpreet singh
DBA - SQL Server

"Noor" wrote:

when you are designing a auditing table , you have to carefully consider the
events which you wanted to store in database. saving hundred 's of event is
nothing but the waste of space . if you are using sql server 2005 sp2 then
you can use ddl auditing. In SQL
2005, a trigger can be created in order to perform any action upon a DDL
statement.

first, create a table to store event log in database

CREATE TABLE admin.logs (
LogID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
Event_Data XML)

create trigger ServerWideLoginLogs
on all server
with execute as self
for LOGON
as begin
DECLARE @event XML
SET @event = eventdata()
INSERT INTO Admin_Log.admin.logs
(EventTime,EventType,LoginName,HostName,AppName,Ev ent_Data)
VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS
VARCHAR(64)) AS DATETIME),

CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS
VARCHAR(100)),

APP_NAME(),

@event)

end

GO

The Event_Data field is populated by the EVENTDATA() function from our
INSERT statement in our trigger. In this case, the eventdata() function
returns XML data related to the connection, including the date and time of
the login, the server name, the user ID, and the machine system ID.

this is simple auditing trigger i am sure it will help you . you can change
it according to your needs it capture the enough events to track down fault.

manpreet singh
DBA - SQL Server

"Noor" wrote:

when you are designing a auditing table , you have to carefully consider the
events which you wanted to store in database. saving hundred 's of event is
nothing but the waste of space . if you are using sql server 2005 sp2 then
you can use ddl auditing. In SQL
2005, a trigger can be created in order to perform any action upon a DDL
statement.

first, create a table to store event log in database

CREATE TABLE admin.logs (
LogID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
Event_Data XML)

create trigger ServerWideLoginLogs
on all server
with execute as self
for LOGON
as begin
DECLARE @event XML
SET @event = eventdata()
INSERT INTO Admin_Log.admin.logs
(EventTime,EventType,LoginName,HostName,AppName,Ev ent_Data)
VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS
VARCHAR(64)) AS DATETIME),

CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS
VARCHAR(100)),

APP_NAME(),

@event)

end

GO

The Event_Data field is populated by the EVENTDATA() function from our
INSERT statement in our trigger. In this case, the eventdata() function
returns XML data related to the connection, including the date and time of
the login, the server name, the user ID, and the machine system ID.

this is simple auditing trigger i am sure it will help you . you can change
it according to your needs it capture the enough events to track down fault.

manpreet singh
DBA - SQL Server

"Noor" wrote:

when you are designing a auditing table , you have to carefully consider the
events which you wanted to store in database. saving hundred 's of event is
nothing but the waste of space . if you are using sql server 2005 sp2 then
you can use ddl auditing. In SQL
2005, a trigger can be created in order to perform any action upon a DDL
statement.

first, create a table to store event log in database

CREATE TABLE admin.logs (
LogID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
Event_Data XML)

create trigger ServerWideLoginLogs
on all server
with execute as self
for LOGON
as begin
DECLARE @event XML
SET @event = eventdata()
INSERT INTO Admin_Log.admin.logs
(EventTime,EventType,LoginName,HostName,AppName,Ev ent_Data)
VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS
VARCHAR(64)) AS DATETIME),

CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS
VARCHAR(100)),

APP_NAME(),

@event)

end

GO

The Event_Data field is populated by the EVENTDATA() function from our
INSERT statement in our trigger. In this case, the eventdata() function
returns XML data related to the connection, including the date and time of
the login, the server name, the user ID, and the machine system ID.

this is simple auditing trigger i am sure it will help you . you can change
it according to your needs it capture the enough events to track down fault.

manpreet singh
DBA - SQL Server

"Noor" wrote:

when you are designing a auditing table , you have to carefully consider the
events which you wanted to store in database. saving hundred 's of event is
nothing but the waste of space . if you are using sql server 2005 sp2 then
you can use ddl auditing. In SQL
2005, a trigger can be created in order to perform any action upon a DDL
statement.

first, create a table to store event log in database

CREATE TABLE admin.logs (
LogID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
Event_Data XML)

create trigger ServerWideLoginLogs
on all server
with execute as self
for LOGON
as begin
DECLARE @event XML
SET @event = eventdata()
INSERT INTO Admin_Log.admin.logs
(EventTime,EventType,LoginName,HostName,AppName,Ev ent_Data)
VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS
VARCHAR(64)) AS DATETIME),

CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS
VARCHAR(100)),

APP_NAME(),

@event)

end

GO

The Event_Data field is populated by the EVENTDATA() function from our
INSERT statement in our trigger. In this case, the eventdata() function
returns XML data related to the connection, including the date and time of
the login, the server name, the user ID, and the machine system ID.

this is simple auditing trigger i am sure it will help you . you can change
it according to your needs it capture the enough events to track down fault.

manpreet singh
DBA - SQL Server

"Noor" wrote:

when you are designing a auditing table , you have to carefully consider the
events which you wanted to store in database. saving hundred 's of event is
nothing but the waste of space . if you are using sql server 2005 sp2 then
you can use ddl auditing. In SQL
2005, a trigger can be created in order to perform any action upon a DDL
statement.

first, create a table to store event log in database

CREATE TABLE admin.logs (
LogID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
Event_Data XML)

create trigger ServerWideLoginLogs
on all server
with execute as self
for LOGON
as begin
DECLARE @event XML
SET @event = eventdata()
INSERT INTO Admin_Log.admin.logs
(EventTime,EventType,LoginName,HostName,AppName,Ev ent_Data)
VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS
VARCHAR(64)) AS DATETIME),

CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS
VARCHAR(100)),

APP_NAME(),

@event)

end

GO

The Event_Data field is populated by the EVENTDATA() function from our
INSERT statement in our trigger. In this case, the eventdata() function
returns XML data related to the connection, including the date and time of
the login, the server name, the user ID, and the machine system ID.

this is simple auditing trigger i am sure it will help you . you can change
it according to your needs it capture the enough events to track down fault.

manpreet singh
DBA - SQL Server

"Noor" wrote:

when you are designing a auditing table , you have to carefully consider the
events which you wanted to store in database. saving hundred 's of event is
nothing but the waste of space . if you are using sql server 2005 sp2 then
you can use ddl auditing. In SQL
2005, a trigger can be created in order to perform any action upon a DDL
statement.

first, create a table to store event log in database

CREATE TABLE admin.logs (
LogID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
Event_Data XML)

create trigger ServerWideLoginLogs
on all server
with execute as self
for LOGON
as begin
DECLARE @event XML
SET @event = eventdata()
INSERT INTO Admin_Log.admin.logs
(EventTime,EventType,LoginName,HostName,AppName,Ev ent_Data)
VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS
VARCHAR(64)) AS DATETIME),

CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS
VARCHAR(100)),

APP_NAME(),

@event)

end

GO

The Event_Data field is populated by the EVENTDATA() function from our
INSERT statement in our trigger. In this case, the eventdata() function
returns XML data related to the connection, including the date and time of
the login, the server name, the user ID, and the machine system ID.

this is simple auditing trigger i am sure it will help you . you can change
it according to your needs it capture the enough events to track down fault.

manpreet singh
DBA - SQL Server

"Noor" wrote:

when you are designing a auditing table , you have to carefully consider the
events which you wanted to store in database. saving hundred 's of event is
nothing but the waste of space . if you are using sql server 2005 sp2 then
you can use ddl auditing. In SQL
2005, a trigger can be created in order to perform any action upon a DDL
statement.

first, create a table to store event log in database

CREATE TABLE admin.logs (
LogID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
Event_Data XML)

create trigger ServerWideLoginLogs
on all server
with execute as self
for LOGON
as begin
DECLARE @event XML
SET @event = eventdata()
INSERT INTO Admin_Log.admin.logs
(EventTime,EventType,LoginName,HostName,AppName,Ev ent_Data)
VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS
VARCHAR(64)) AS DATETIME),

CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS
VARCHAR(100)),

APP_NAME(),

@event)

end

GO

The Event_Data field is populated by the EVENTDATA() function from our
INSERT statement in our trigger. In this case, the eventdata() function
returns XML data related to the connection, including the date and time of
the login, the server name, the user ID, and the machine system ID.

this is simple auditing trigger i am sure it will help you . you can change
it according to your needs it capture the enough events to track down fault.

manpreet singh
DBA - SQL Server

"Noor" wrote:

when you are designing a auditing table , you have to carefully consider the
events which you wanted to store in database. saving hundred 's of event is
nothing but the waste of space . if you are using sql server 2005 sp2 then
you can use ddl auditing. In SQL
2005, a trigger can be created in order to perform any action upon a DDL
statement.

first, create a table to store event log in database

CREATE TABLE admin.logs (
LogID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
Event_Data XML)

create trigger ServerWideLoginLogs
on all server
with execute as self
for LOGON
as begin
DECLARE @event XML
SET @event = eventdata()
INSERT INTO Admin_Log.admin.logs
(EventTime,EventType,LoginName,HostName,AppName,Ev ent_Data)
VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS
VARCHAR(64)) AS DATETIME),

CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS
VARCHAR(100)),

APP_NAME(),

@event)

end

GO

The Event_Data field is populated by the EVENTDATA() function from our
INSERT statement in our trigger. In this case, the eventdata() function
returns XML data related to the connection, including the date and time of
the login, the server name, the user ID, and the machine system ID.

this is simple auditing trigger i am sure it will help you . you can change
it according to your needs it capture the enough events to track down fault.

manpreet singh
DBA - SQL Server

"Noor" wrote: