· 6 years ago · Jul 29, 2019, 05:34 PM
1DECLARE @cmd nvarchar(max);
2SET @cmd = N'';
3SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N'
4UNION ALL
5' END + N'SELECT dp.sid
6FROM ' + QUOTENAME(d.name) + N'.sys.database_principals dp
7WHERE dp.sid IS NOT NULL
8'
9FROM sys.databases d
10WHERE d.state_desc = N'ONLINE';
11
12PRINT @cmd;
13
14DROP TABLE IF EXISTS #database_sids;
15CREATE TABLE #database_sids
16(
17 [sid] int NOT NULL
18);
19INSERT INTO #database_sids ([sid])
20EXEC sys.sp_executesql @cmd;
21
22SELECT sp.name
23 , sp.type_desc
24FROM sys.server_principals sp
25WHERE NOT EXISTS (
26 SELECT 1
27 FROM #database_sids ds
28 WHERE ds.sid = sp.sid
29 )
30 AND sp.[type_desc] NOT IN (
31 N'SERVER_ROLE'
32 , N'CERTIFICATE_MAPPED_LOGIN'
33 )
34ORDER BY sp.name;
35
36exec sp_MSforeachdb
37'Use [?];
38select DB_NAME () as DBName, dp.name,
39 dp.principal_id,
40 dp.type,
41 dp.type_desc,
42 dp.sid,
43 sp.sid,
44 ''use '' + cast(DB_NAME () as varchar(50)) + ''; if not exists (select containment from sys.databases where not containment = 0) drop user ['' + dp.name + '']'' as FixCommand
45from sys.database_principals as dp
46 left outer join sys.server_principals as sp on dp.sid = sp.sid
47where dp.principal_id > 4 AND sp.sid is null and dp.type in (''S'', ''U'', ''G'')'
48go
49
50select DB_NAME () as DBName, dp.name,
51 dp.principal_id,
52 dp.type,
53 dp.type_desc,
54 dp.sid,
55 sp.sid,
56 'drop user [' + dp.name + ']' as FixCommand
57from sys.database_principals as dp
58 left outer join sys.server_principals as sp on dp.sid = sp.sid
59where dp.principal_id > 4 AND sp.sid is null and dp.type in ('S', 'U', 'G')
60
61Declare @Temp Table (sid varbinary(85), name nvarchar (100) );
62
63insert into @Temp (sid, name)
64
65exec sp_MSforeachdb
66'Use [?];
67select sid, name
68from sys.database_principals as dp
69where db_id () > 4 and type in (''S'', ''U'', ''G'') and sid is not null and (dp.name not in (''dbo'', ''guest'') and dp.name not like ''##%'' ) '
70
71
72select
73 sp.name,
74 sp.sid,
75 sp.type_desc,
76 'drop Login [' + sp.name + ']' as FixCommand
77from sys.server_principals as sp
78 left outer join @Temp as dp on sp.sid = dp.sid
79Where sp.type in ('S', 'U', 'G') and ( not sp.name = 'sa' and sp.name not like '##%' and sp.name not like 'NT %')and dp.name is null
80GO
81
82-- create sp_helplogins with only first resultset
83
84create procedure #sp_helplogins
85
86 @LoginNamePattern sysname = NULL
87AS
88
89set nocount on
90
91declare
92 @exec_stmt nvarchar(3550)
93
94declare
95 @RetCode int
96 ,@CountSkipPossUsers int
97 ,@Int1 int
98
99declare
100 @c10DBName sysname
101 ,@c10DBStatus int
102 ,@c10DBSID varbinary(85)
103
104declare
105 @charMaxLenLoginName varchar(11)
106 ,@charMaxLenDBName varchar(11)
107 ,@charMaxLenUserName varchar(11)
108 ,@charMaxLenLangName varchar(11)
109
110declare
111 @DBOptLoading int --0x0020 32 "DoNotRecover"
112 ,@DBOptPreRecovery int --0x0040 64
113 ,@DBOptRecovering int --0x0080 128
114
115 ,@DBOptSuspect int --0x0100 256 ("not recovered")
116 ,@DBOptOffline int --0x0200 512
117 ,@DBOptDBOUseOnly int --0x0800 2048
118
119 ,@DBOptSingleUser int --0x1000 4096
120
121
122------------- create work holding tables ----------------
123-- Create temp tables before any DML to ensure dynamic
124
125CREATE TABLE #tb2_PlainLogins
126 (
127 LoginName sysname collate catalog_default NOT Null
128 ,SID varchar(85) collate catalog_default NOT Null
129 ,DefDBName sysname collate catalog_default Null
130 ,DefLangName sysname collate catalog_default Null
131 ,AUser char(5) collate catalog_default Null
132 ,ARemote char(7) collate catalog_default Null
133 )
134
135CREATE TABLE #tb1_UA
136 (
137 LoginName sysname collate catalog_default NOT Null
138 ,DBName sysname collate catalog_default NOT Null
139 ,UserName sysname collate catalog_default NOT Null
140 ,UserOrAlias char(8) collate catalog_default NOT Null
141 )
142
143---------------- Initial data values -------------------
144
145select
146 @RetCode = 0 -- 0=good ,1=bad
147 ,@CountSkipPossUsers = 0
148
149
150---------------- Only SA can run this -------------------
151
152
153if (not (is_srvrolemember('securityadmin') = 1))
154 begin
155 raiserror(15247,-1,-1)
156 select @RetCode = 1
157 goto label_86return
158 end
159
160---------------------- spt_values ----------------
161-------- 'D'
162
163select @DBOptLoading = number
164 from master.dbo.spt_values
165 where type = 'D'
166 and name = 'loading'
167
168select @DBOptPreRecovery = number
169 from master.dbo.spt_values
170 where type = 'D'
171 and name = 'pre recovery'
172
173select @DBOptRecovering = number
174 from master.dbo.spt_values
175 where type = 'D'
176 and name = 'recovering'
177
178select @DBOptSuspect = number
179 from master.dbo.spt_values
180 where type = 'D'
181 and name = 'not recovered'
182
183select @DBOptOffline = number
184 from master.dbo.spt_values
185 where type = 'D'
186 and name = 'offline'
187
188select @DBOptDBOUseOnly = number
189 from master.dbo.spt_values
190 where type = 'D'
191 and name = 'dbo use only'
192
193select @DBOptSingleUser = number
194 from master.dbo.spt_values
195 where type = 'D'
196 and name = 'single user'
197
198
199
200--------------- Cursor, for DBNames -------------------
201
202
203declare ms_crs_10_DB
204 Cursor local static For
205select
206 name ,status ,sid
207 from
208 master.dbo.sysdatabases
209
210
211
212OPEN ms_crs_10_DB
213
214
215----------------- LOOP 10: thru Databases ------------------
216
217
218--------------
219WHILE (10 = 10)
220 begin --LOOP 10: thru Databases
221
222
223 FETCH
224 next
225 from
226 ms_crs_10_DB
227 into
228 @c10DBName
229 ,@c10DBStatus
230 ,@c10DBSID
231
232
233 IF (@@fetch_status <> 0)
234 begin
235 deallocate ms_crs_10_DB
236 BREAK
237 end
238
239
240-------------------- Okay if we peek inside this DB now?
241
242
243 IF ( @c10DBStatus & @DBOptDBOUseOnly > 0
244 AND @c10DBSID <> suser_sid()
245 )
246 begin
247 select @CountSkipPossUsers = @CountSkipPossUsers + 1
248 CONTINUE
249 end
250
251
252 IF (@c10DBStatus & @DBOptSingleUser > 0)
253 begin
254
255 select @Int1 = count(*)
256 from sys.dm_exec_requests
257 where session_id <> @@spid
258 and database_id = db_id(@c10DBName)
259
260 IF (@Int1 > 0)
261 begin
262 select @CountSkipPossUsers = @CountSkipPossUsers + 1
263 CONTINUE
264 end
265 end
266
267
268 IF (@c10DBStatus &
269 (
270 @DBOptLoading
271 | @DBOptRecovering
272 | @DBOptSuspect
273 | @DBOptPreRecovery
274 )
275 > 0
276 )
277 begin
278 select @CountSkipPossUsers = @CountSkipPossUsers + 1
279 CONTINUE
280 end
281
282
283 IF (@c10DBStatus &
284 (
285 @DBOptOffline
286 )
287 > 0
288 )
289 begin
290 --select @CountSkipPossUsers = @CountSkipPossUsers + 1
291 CONTINUE
292 end
293
294 IF (has_dbaccess(@c10DBName) <> 1)
295 begin
296 raiserror(15622,-1,-1, @c10DBName)
297 CONTINUE
298 end
299
300
301
302--------------------- Add the User info to holding table.
303 select @exec_stmt = '
304 INSERT #tb1_UA
305 (
306 DBName
307 ,LoginName
308 ,UserName
309 ,UserOrAlias
310 )
311 select
312 N' + quotename(@c10DBName, '''') + '
313 ,l.name
314 ,u.name
315 ,''User''
316 from
317 ' + quotename(@c10DBName, '[') + '.sys.sysusers u
318 ,sys.server_principals l
319 where
320 u.sid = l.sid' +
321 case
322 when @LoginNamePattern is null
323 then ''
324 else ' and ( l.name = N' + quotename(@LoginNamePattern , '''') + '
325 or l.name = N' + quotename(@LoginNamePattern , '''') + ')'
326 end
327 +
328' UNION
329 select
330
331 N' + quotename(@c10DBName, '''') + '
332 ,l.name
333 ,u2.name
334 ,''MemberOf''
335 from
336 ' + quotename(@c10DBName, '[')+ '.sys.database_role_members m
337 ,' + quotename(@c10DBName, '[')+ '.sys.database_principals u1
338 ,' + quotename(@c10DBName, '[')+ '.sys.database_principals u2
339 ,sys.server_principals l
340 where
341 u1.sid = l.sid
342 and m.member_principal_id = u1.principal_id
343 and m.role_principal_id = u2.principal_id' +
344 case
345 when @LoginNamePattern is null
346 then ''
347 else ' and ( l.name = N' + quotename(@LoginNamePattern , '''') + '
348 or l.name = N' + quotename(@LoginNamePattern , '''') + ')'
349 end
350
351 EXECUTE(@exec_stmt)
352
353 end --loop 10
354
355--------------- Populate plain logins work table ---------------
356
357
358INSERT #tb2_PlainLogins
359 (
360 LoginName
361 ,SID
362 ,DefDBName
363 ,DefLangName
364 ,AUser
365 ,ARemote
366 )
367 select
368 loginname
369 ,convert(varchar(85), sid)
370 ,dbname
371 ,language
372 ,Null
373 ,Null
374 from
375 master.dbo.syslogins
376 where
377 @LoginNamePattern is null
378 or name = @LoginNamePattern
379 or loginname = @LoginNamePattern
380
381
382-- AUser
383
384UPDATE #tb2_PlainLogins --(1996/08/12)
385 set
386 AUser = 'yes'
387 from
388 #tb2_PlainLogins
389 ,#tb1_UA tb1
390 where
391 #tb2_PlainLogins.LoginName = tb1.LoginName
392 and #tb2_PlainLogins.AUser IS Null
393
394
395
396UPDATE #tb2_PlainLogins
397 set
398 AUser =
399 CASE @CountSkipPossUsers
400 When 0 Then 'NO'
401 Else '?'
402 END
403 where
404 AUser IS Null
405
406
407-- ARemote
408
409UPDATE #tb2_PlainLogins
410 set
411 ARemote = 'YES'
412 from
413 #tb2_PlainLogins
414 ,master.dbo.sysremotelogins rl
415 where
416 #tb2_PlainLogins.SID = rl.sid
417 and #tb2_PlainLogins.ARemote IS Null
418
419
420
421UPDATE #tb2_PlainLogins
422 set
423 ARemote = 'no'
424 where
425 ARemote IS Null
426
427
428
429------------ Optimize widths for plain Logins report ----------
430
431
432select
433 @charMaxLenLoginName =
434 convert ( varchar
435 ,isnull ( max(datalength(LoginName)) ,9)
436 )
437 ,@charMaxLenDBName =
438 convert ( varchar
439 , isnull (max(isnull (datalength(DefDBName) ,9)) ,9)
440 )
441 ,@charMaxLenLangName =
442 convert ( varchar
443 , isnull (max(isnull (datalength(DefLangName) ,11)) ,11)
444 )
445 from
446 #tb2_PlainLogins
447
448
449
450---------------- Print out plain Logins report -------------
451
452EXEC(
453'
454set nocount off
455
456
457select
458 ''LoginName'' = substring (LoginName ,1 ,'
459 + @charMaxLenLoginName + ')
460
461 ,''SID'' = convert(varbinary(85), SID)
462
463 ,''DefDBName'' = substring (DefDBName ,1 ,'
464 + @charMaxLenDBName + ')
465
466 ,''DefLangName'' = substring (DefLangName ,1 ,'
467 + @charMaxLenLangName + ')
468
469 ,AUser
470 ,ARemote
471 from
472 #tb2_PlainLogins
473 order by
474 LoginName
475
476
477Set nocount on
478'
479)
480
481----------------------- Finalization --------------------
482label_86return:
483
484IF (object_id('#tb2_PlainLogins') IS NOT Null)
485 DROP Table #tb2_PlainLogins
486
487IF (object_id('#tb1_UA') IS NOT Null)
488 DROP Table #tb1_UA
489
490Return @RetCode -- sp_helplogins
491GO
492
493
494-- get logins which does't have user mappings to any database
495CREATE TABLE #Logins (
496 LoginName sysname NULL,
497 SID varbinary(85) NULL,
498 DefDBName sysname NULL,
499 DefLangName sysname NULL,
500 Auser char(5) NULL,
501 ARemote char(7) NULL
502);
503
504INSERT #Logins
505EXEC #sp_helplogins;
506
507SELECT *
508FROM #Logins
509WHERE Auser = 'NO';
510
511DROP TABLE #Logins;
512
513DROP procedure #sp_helplogins;