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)

T-SQL to get user list for every database

Today I created a new T-SQL script that gets list of existing databases on SQL Server and via sp_helpuser queries each database for login information.

There is also used variable table @dbs which keeps database names with assigned IDs. IDs are being assigned with row_number() procedure.

It could be further developed to join logins to some temporary table etc, that would allow to see some statistics (user rights, user and database mappings).

declare @RowCnt int
declare @MaxRows int
declare @dbname nvarchar(50)

declare @dbs table
(
 ID int,
 [name] nvarchar(50)
)

insert into @dbs
select * from
(select row_number() over (ORDER BY [name]) as 'ID', 
[name] FROM master.dbo.sysdatabases WHERE dbid > 6) as tbl1

select @MaxRows=count(*) from @dbs
select @RowCnt = 1

while @RowCnt <= @MaxRows
begin
 select @dbname = [name] from @dbs where id = @RowCnt
 exec('use ['+@dbname+']; EXECUTE sp_helpuser;')
 Select @RowCnt = @RowCnt + 1
end