· 7 years ago · Oct 11, 2018, 05:28 PM
1IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE type = 'U' AND name = 'Audit')
2 CREATE TABLE dbo.Audit
3 (
4 login_name SYSNAME NOT NULL PRIMARY KEY CLUSTERED,
5 login_sid VARBINARY(85) NULL,
6 login_type NVARCHAR(60) NOT NULL,
7 login_group SYSNAME NULL,
8 login_created DATETIME NOT NULL,
9 login_modified DATETIME NOT NULL,
10 login_time DATETIME NULL,
11 is_sysadmin BIT NOT NULL,
12 is_disabled BIT NOT NULL
13 );
14
15DECLARE @GroupMemberList TABLE (login_name SYSNAME, login_type NVARCHAR(60), privilege CHAR(9), mapped_name SYSNAME, login_group SYSNAME, is_disabled BIT, login_time DATETIME, login_created DATETIME, login_modified DATETIME, login_sid VARBINARY(85));
16
17DECLARE @GroupName SYSNAME;
18
19DECLARE GroupCursor CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
20SELECT
21 name
22FROM
23 sys.server_principals
24WHERE
25 type = 'G';
26
27OPEN GroupCursor;
28
29FETCH NEXT FROM GroupCursor INTO @GroupName;
30
31WHILE @@FETCH_STATUS = 0
32 BEGIN
33 INSERT INTO @GroupMemberList(login_name, login_type, privilege, mapped_name, login_group)
34 EXECUTE xp_logininfo @GroupName,'members';
35
36 FETCH NEXT FROM GroupCursor INTO @GroupName;
37 END;
38
39CLOSE GroupCursor;
40DEALLOCATE GroupCursor;
41
42INSERT INTO @GroupMemberList(login_name, login_type, privilege, mapped_name, login_group, is_disabled, login_created, login_modified, login_sid)
43SELECT
44 p.name, p.type_desc, CASE l.sysadmin WHEN 1 THEN 'admin' ELSE 'user' END, p.name, '', p.is_disabled, p.create_date, p.modify_date, p.sid
45FROM
46 sys.server_principals p
47INNER JOIN sys.syslogins l
48 ON p.sid = l.sid
49WHERE
50 type IN ('U','S');
51
52WITH Logins AS
53(
54SELECT
55 ROW_NUMBER() OVER (PARTITION BY login_name ORDER BY login_type DESC, [privilege] ASC) [RowNumber],
56 *
57FROM
58 @GroupMemberList
59)
60DELETE
61 Logins
62WHERE
63 RowNumber > 1 OR
64 login_name LIKE '##%' OR
65 login_name LIKE 'NT %';
66
67WITH LastLogin AS
68(
69 SELECT login_name,login_time,ROW_NUMBER() OVER (PARTITION BY login_name ORDER BY login_time DESC) [RowNumber] FROM sys.dm_exec_sessions
70)
71UPDATE
72 g
73SET
74 login_time = ll.login_time
75FROM
76 @GroupMemberList g
77INNER JOIN LastLogin ll
78 ON g.login_name = ll.login_name AND
79 ll.RowNumber = 1;
80
81UPDATE
82 g
83SET
84 login_created = create_date,
85 login_modified = modify_date
86FROM
87 @GroupMemberList g
88INNER JOIN sys.server_principals p
89 ON g.login_group = p.name
90
91MERGE dbo.Audit t
92USING (SELECT * FROM @GroupMemberList) s
93 ON t.login_name = s.login_name
94WHEN NOT MATCHED BY TARGET THEN
95 INSERT
96 (
97 login_name,
98 login_sid,
99 login_type,
100 login_group,
101 login_created,
102 login_modified,
103 login_time,
104 is_sysadmin,
105 is_disabled
106 )
107 VALUES
108 (
109 s.login_name,
110 s.login_sid,
111 CASE s.login_type
112 WHEN 'user' THEN 'WINDOWS_GROUP'
113 ELSE s.login_type
114 END,
115 NULLIF(s.login_group,''),
116 s.login_created,
117 s.login_modified,
118 s.login_time,
119 CASE s.privilege
120 WHEN 'admin' THEN 1
121 ELSE 0
122 END,
123 ISNULL(s.is_disabled,0)
124 )
125WHEN MATCHED THEN
126 UPDATE SET
127 login_type = CASE s.login_type
128 WHEN 'user' THEN 'WINDOWS_GROUP'
129 ELSE s.login_type
130 END,
131 login_sid = s.login_sid,
132 login_group = NULLIF(s.login_group,''),
133 login_created = s.login_created,
134 login_modified = s.login_modified,
135 login_time = s.login_time,
136 is_sysadmin = CASE s.privilege
137 WHEN 'admin' THEN 1
138 ELSE 0
139 END,
140 is_disabled = ISNULL(s.is_disabled,0)
141WHEN NOT MATCHED BY SOURCE THEN
142 DELETE;
143
144SELECT * FROM dbo.Audit