Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Create SQL table for logging user connections with permissions and compression on the logging table.
CREATE DATABASE UserIPAudit
GO
USE UserIPAudit
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UserIPLog](
[id] [int] IDENTITY(1,1) NOT NULL,
[LogonTime] [datetime] NULL,
[LoginName] [nvarchar](max) NULL,
[ClientHost] [varchar](50) NULL,
[LoginType] [varchar](100) NULL,
[AppName] [nvarchar](500) NULL,
[FullLog] [xml] NULL,
CONSTRAINT [PK_IP_Log] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[UserIPLog] ADD CONSTRAINT [DF_IP_Log_LogonTime] DEFAULT (getdate()) FOR [LogonTime]
GO
use [UserIPAudit]
GO
GRANT INSERT TO [public]
GO
use [UserIPAudit]
GO
GRANT CONNECT TO [public]
GO
USE [UserIPAudit]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [public]
GO
USE [UserIPAudit]
ALTER TABLE [dbo].[UserIPLog] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = PAGE)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.