T-SQL script to get statistics for mappings between databases and users

I am continuing the development of T-SQL script that I did in my previous post. A big colleague of mine pointed out how to run that script just in one row :) I was really amazed!!!

exec sp_MSForEachDB @command1='use [?] exec sp_helpuser'

So it shows the logins information for each database. I've developed it and have got one big table that keeps database, login , user, group, schema names, user IDs and SID.

I used object tables instead of variable tables, because it is not able to work with variable tables under the sp_MSForEachDB procedure.

Here it is:

create table #results
(
 [UserName] nvarchar(100),
 [GroupName] nvarchar(100),
 [LoginName] nvarchar(100),
 [DefDBName] nvarchar(100),
 [DefSchemaName] nvarchar(100),
 [UserID] int,
 [SID] varbinary(85)
)

create table #users
(
 [DBName] nvarchar(100),
 [UserName] nvarchar(100),
 [GroupName] nvarchar(100),
 [LoginName] nvarchar(100),
 [DefDBName] nvarchar(100),
 [DefSchemaName] nvarchar(100),
 [UserID] int,
 [SID] varbinary(85)
)

exec sp_MSForEachDB @command1='
use [?]
delete from #results
insert into #results 
 exec sp_helpuser

insert into #users
 select db_name(), * from #results'

And here is some useful queries for getting statistics.

Next query shows amount of databases to which the user is allowed to connect:

select LoginName, count(DBName) as Databases from #users
 where LoginName is not null
 group by LoginName order by Databases desc

Shows the databases which do not have any SQL user mappings, so users cant connect them:

select DBName from #users where DBName not in
 (select DBName from #users where loginName is not NULL group by dbname)
 group by DBName

Shows the users which are not mapped to any database:

select loginname from master.dbo.syslogins
 where loginname not in
 (select loginname from #users where loginname is not null group by loginname)