USE master
go
CREATE DATABASE PlayAudit
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN PlayAudit$owner ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN PlayAudit$owner DISABLE
DENY CONNECT SQL TO PlayAudit$owner
ALTER AUTHORIZATION ON DATABASE::PlayAudit TO PlayAudit$owner
go
-------------------------------------------------------------------------------------------------------
USE PlayAudit
go
CREATE TABLE loginaudit (
   auditid             bigint        NOT NULL IDENTITY,
   spid                int           NOT NULL
      CONSTRAINT def_loginaudit_spid DEFAULT @@spid,
   username            sysname       NOT NULL
      CONSTRAINT def_loginaudit_username DEFAULT original_login(),
   logintime           datetime2(3)  NOT NULL
      CONSTRAINT def_loginaudit_logintime DEFAULT sysdatetime(),
   appname             sysname       NULL
      CONSTRAINT def_loginaudit_appname DEFAULT app_name(),
   hostname            sysname       NULL
      CONSTRAINT def_loginaudit_hostname DEFAULT host_name(),
   client_host         varchar(48)   NULL
      CONSTRAINT def_loginaudit_client_host DEFAULT
         eventdata().value('(/EVENT_INSTANCE/ClientHost/text())[1]', 'varchar(48)'),
   is_pooled           bit           NULL
      CONSTRAINT def_loginaudit_is_pooled DEFAULT
         eventdata().value('(/EVENT_INSTANCE/IsPooled/text())[1]', 'bit'),
   -- Extra demo columns!
   token_name          sysname       NOT NULL,
   token_type          nvarchar(128) NOT NULL,
   usage               nvarchar(128) NOT NULL,
   CONSTRAINT pk_loginaudit PRIMARY KEY(auditid)
)
go
-------------------------------------------------------------------------------------------------------
USE master
go
CREATE TRIGGER logintri ON ALL SERVER WITH EXECUTE AS 'PlayAudit$owner' FOR LOGON AS
  INSERT PlayAudit.dbo.loginaudit (token_name, token_type, usage)
     -- DEFAULT VALUES
     SELECT name, type, usage
     FROM   sys.login_token 
go
-------------------------------------------------------------------------------------------------------
SELECT * FROM PlayAudit.dbo.loginaudit      
go
-------------------------------------------------------------------------------------------------------
DROP TRIGGER logintri ON ALL SERVER