In diesem Blog Eintrag geht es um Logon-Trigger. Analog zu den Einstell-Möglichkeiten im Microsoft Active Directory, wo man Benutzern verschiedene Zeiten voreinstellen kann, wann eine Anmeldung möglich ist, kann dies auch im SQL Server nachvollzogen werden.

Hierfür ist lediglich eine kleine Infrastruktur notwendig. Eine Verwaltungsdatenbank in der die Benutzer gespeichert werden, wie auch eine Tabelle in der die Zeiten definiert werden.

In folgendem Beispiel wird die Verwaltungsdatenbank erstellt mit einer Benutzertabelle in der die Benutzernamen gespeichert werden, eine Gruppentabelle um Benutzer zu Gruppen hinzuzufügen die gleichzeitig die Zeiten definieren.

Mit diesem Code wird die Benutzer Tabelle erzeugt. Hier werden lediglich die Benutzernamen gespeichert:
create table dbo.benutzer (
uid uniqueidentifier default newid() primary key not null,
benutzername varchar(255) not null unique);
go

Hier noch 2 Benutzer:
insert into benutzer (benutzername) values ('Herta');
insert into benutzer (benutzername) values ('Kerrin');
go

Um das Beispiel testen zu können, empfiehlt es sich auch diese Benutzer als SQL Authentifizierte Accounts auf dem Server anzulegen und der Datenbank zuzuordnen.

Nun wird die Gruppen Tabelle erstellt. Über die Felder Beginn- und Endzeit wird die Zeit definiert, wo eine Anmeldung möglich ist.
create table dbo.gruppen (
uid uniqueidentifier default newid() primary key not null,
gruppenname varchar(255) not null unique,
beginnzeit time not null,
endzeit time not null);
go

Nun werden noch 3 Gruppen angelegt:
insert into gruppen (gruppenname, beginnzeit, endzeit) values ('Vormittag', '07:00', '12:30');
insert into gruppen (gruppenname, beginnzeit, endzeit) values ('Nachmittag', '12:30', '18:30');
insert into gruppen (gruppenname, beginnzeit, endzeit) values ('Vollzeit', '07:00', '18:30');
go

Zuletzt wird noch die Zuordnungstabelle erstellt:
(Für die Übersicht habe ich hier die Referentiellen Integritäten nicht erstellt)
create table bennutzer_gruppen (
benutzer_uid uniqueidentifier not null,
gruppen_uid uniqueidentifier not null);
go

Nun werden die Benutzer noch jeweils einer Gruppe zugeordnet:
(Beim Test müssen die ID’s angepasst werden)
insert into bennutzer_gruppen values ('E4727227-7C64-46E0-8233-CE0A249AAAB5', 'D319D6EA-D280-4E65-8AC6-823100270667');
insert into bennutzer_gruppen values ('ED3EEA47-E4BA-4E82-A6E7-D5A69E991C95', 'F5D9E222-98BE-455A-B5B5-62096CA254EE');
go

Nun wird noch eine View erstellt, die die Benutzer zu den jeweligen Gruppen ausgibt:
create view benutzerInGruppen as
SELECT     benutzer.benutzername, gruppen.gruppenname, gruppen.beginnzeit, gruppen.endzeit
FROM         bennutzer_gruppen INNER JOIN
benutzer ON bennutzer_gruppen.benutzer_uid = benutzer.uid INNER JOIN
gruppen ON bennutzer_gruppen.gruppen_uid = gruppen.uid
go

Ergebnis:

Für den Trigger wird hier noch eine weitere View erstellt, die prüfen soll, ob es den Benutzer auch gibt:
create view benutzernamen as
SELECT benutzername FROM benutzer WHERE benutzername = SUSER_NAME()
go

Damit die Benutzer beim Login auch prüfen können, ob ein Login möglich ist, wird noch eine Rolle erstellt die auf die beiden Sichten berechtigt und die Benutzer werden dieser Rolle hinzugefügt:
create role benutzer;
grant select on benutzerInGruppen to benutzer
grant select on benutzernamen to benutzer
exec sp_addrolemember benutzer, herta;
exec sp_addrolemember benutzer, kerrin;
go

Ob die Datenbank Infrastruktur stimmt, kann mit folgenden Code überprüft werden:
EXECUTE AS LOGIN = 'kerrin'
SELECT * FROM benutzernamen
REVERT
Hiermit wird die Abfrage unter dem angegeben Benutzerkontext ausgeführt.

Nun wird der Servertrigger erstellt. Server Trigger befinden sich im SSMS hier:

Der Trigger soll folgende Voraussetzungen erfüllen:

• Benutzer die nicht in der Tabelle definiert sind, sollen vom Trigger ausgenommen werden
• Nur Benutzer die sich im Zeitfenster befinden dürfen sich anmelden

Nun der Code:
CREATE trigger benutzerAnmeldung on ALL SERVER
for LOGON
as
begin
DECLARE @result INT
IF((SELECT COUNT(*) from LogonTime.dbo.benutzernamen) > 0)
BEGIN
select @result = COUNT(*) from LogonTime.dbo.benutzerInGruppen
where benutzername = SUSER_SNAME()
AND (
beginnzeit <= CONVERT(time, GETDATE()) AND endzeit >= CONVERT(time, GETDATE()))
IF(@result = 0)
BEGIN
ROLLBACK;
END
END
END;
go

Nun kann man den Logon mit einem Benutzernamen testen. Befindet man sich in dem Zeitfenster funktioniert eine Anmeldung, ansonsten wird man durch folgende Meldung begrüßt:

In diesem Blog Eintrag geht es um Change Data Capture (CDC). Dieses Werkzeug im Microsoft SQL Server 2008 (verfügbar (leider) ab der Enterprise Edition) dient nicht für das Autiting von Benutzern sondern um Daten zu versionieren und so Veränderungen oder Bewegungen auswerten oder wiederherstellen zu können. Das bedeutet, jede Tabelle in der CDC aktiviert ist, wird entsprecht mit protokolliert und die Änderungen werden in einer eigenen Systemtabelle pro Objekt gespeichert. Wie man zu erst vermuten möchte, handelt es sich nicht um typische DML Trigger sondern um eine Technologie, die direkt vom Transaktionslog liest und somit sicher die Datenbewegungen registriert.

Als Fallbeispiel wird eine Buchungstabelle „überwacht“:
CREATE TABLE dbo.buchungen (
uid uniqueidentifier default newid() not null primary key,
buchungsid int not null,
buchungswert money not null,
buchungsdatum datetime not null);
GO

Über folgenden Befehl wird CDC in der Datenbank aktiviert:
EXEC sys.sp_cdc_enable_db
GO

Nun wird CDC für die Tabelle aktiviert:
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'buchungen',
@role_name     = NULL,
@supports_net_changes = 1
GO

Nun fügen wir einen Datensatz in die Buchungstabelle ein:
INSERT INTO dbo.buchungen (buchungsid, buchungswert, buchungsdatum)
VALUES (1, 200, GETDATE());
GO

Zuletzt verändern wir diesen Datensatz nochmal:
UPDATE dbo.buchungen SET buchungswert = 17300 WHERE buchungsid = 1
GO

Mit folgender Abfrage kann die Datenveränderung dargestellt werden mit den jeweiligen Statis:
SELECT
CASE __$operation
WHEN 1 THEN 'DELETED'
WHEN 2 THEN 'INSERTED'
WHEN 3 THEN 'BEFORE UPDATED'
WHEN 4 THEN 'AFTER UPDATED'
END 'Aktion', buchungsid, buchungswert, buchungsdatum FROM cdc.dbo_buchungen_CT

Ergebnis:

Weitere Informationen zu den Statis gibt es hier:
http://technet.microsoft.com/en-us/library/bb522511.aspx

Sobald CDC aktiviert wird, werden folgende Tabellen erstellt:

• cdc.change_tables
  Enthält alle CDC überwachten Tabellen
• cdc.captured_columns
  Enthält die Felder der CDC überwachten Tabellen
• cdc.dbo_buchungen_CT
  Eine solche Tabelle wird für jedes CDC Objekt erstellt und speichert die Datenänderungen
• cdc.ddl_history
  Speichert Änderungen an der CDC überwachten Tabelle
• cdc.index_columns
  Enthält die Index Felder der CDC Tabellen
• cdc.lsn_time_mapping
  Über diese Tabelle kann nachvollzogen werden, wann welche Änderung durchgeführt wurde. Dies kann anhand der LSN mit der Benutzertabelle verglichen werden.

Weitere Informationen zu den Tabellen gibt es hier: http://technet.microsoft.com/de-de/library/bb500353.aspx

Weiterhin erstellt CDC noch zwei SQL Agent Jobs:

1. cdc.CDC_capture
   Dieser Job steuert CDC für die jeweiligen konfigurierten Objekte die für CDC aktiviert worden sind
2. cdc.CDC_cleanup
   Entfernt abgelaufene, bzw. deaktivierte CDC Objekte die sonst vom Capture Job ausgeführt werden

Hinweis:
Das Wiederherstellungsmodell für CDC ist nicht relavant. CDC liest lediglich aus dem Aktiven Teil des Transaktionslog und somit wir auch das Einfache Wiederherstellungsmodell unterstützt.

Fazit:
CDC eignet sich hervorragend für Auswertungszwecke um Datenveränderungen zu speichern und darzustellen. Eine Speicherung von Benutzernamen etc. ist nicht vorgesehen und könnte max. selber durch DML Trigger hinzugeführt werden.