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 descShows 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 DBNameShows 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)
Posts
