DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS

 

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:

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

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

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE