Logins & Users including Orphans

SQL Server 2005 has 2 HELP system stored procedures to list server logins and users within a database.

Sp_helplogins lists all logins. In the second result set it lists all login usage within databases as users. If you don’t look carefully, you may miss the second result set altogether.

Sp_helpuser lists all users within a database with information such as group membership.

SQL Server 2005 allows to drop (remove) a login even if it being used in a database, thus creating database users orphans. These orphans, frequent source of operational problems, don’t show up in sp_helplogins, but only in sp_helpuser . That requires visiting each database and locating orphans by comparing the database user logins to live logins.

The stored procedure LoginsAndUsers below solves this problem. It creates a single result set with all logins and all users listed.  Orphaned database users are listed as well. The corresponding server is login is NULL for orphaned users.


drop proc LoginsAndUsers
go
create proc LoginsAndUsers
as
begin
create table #systemdbs (name sysname)
insert #systemdbs
select 'master'
union select 'msdb'
union select 'model'
union select 'tempdb'

create table #dbusers (DatabaseName sysname, UserName sysname, GroupName sysname, LoginName sysname null,
DefDBName sysname null, DefSchemaName sysname null, UserID int, SID varbinary(max))

create table #dbusersbuffer ( UserName sysname, GroupName sysname, LoginName sysname null,
DefDBName sysname null, DefSchemaName sysname null, UserID int, SID varbinary(max))



declare @Command nvarchar(1012)
set @Command= 'if not exists (select * from #systemdbs where name = ''?'') begin '+char(13)+
+'use ? ; insert #dbusersbuffer exec sp_helpuser'+char(13)
+'insert #dbusers select ''?'', * from #dbusersbuffer'+char(13)
+'truncate table #dbusersbuffer'+char(13)
+'end'
print @Command

exec sp_MSforeachdb @command1 = @Command

select * from #dbusers order by LoginName, UserName

drop table #dbusers
drop table #dbusersbuffer
drop table #systemdbs
end
go

exec LoginsAndUsers

Sample Partial Output:

DatabaseName	UserName	GroupName	LoginName	DefDBName	DefSchemaName	UserID
AdventureWorks	msmith	public	msmith	AdventureWorks	dbo	5
AdventureWorksDW	msmith	public	msmith	AdventureWorks	dbo	6
standbyAW	msmith	public	msmith	AdventureWorks	dbo	5