Listing All Logins and Users Including Orphans

By Kalman Toth, M.Phil. Physics, M.Phil. Comp. Science, MCDBA, MCITP

March 24, 2007

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:

Related articles:

SQL Server Logins and Users

How to: Create a SQL Server Login

How to transfer logins and passwords between instances of SQL Server