Find user in all database with Sp_MSforEachDB in SQL Server 2005

I just assisted one person in one of the forum who wanted the list of all databases which are having user “Guest”. Since this is something needs to iterate between all available databases in SQL Server except “Master” and “TempDB”, I thought to do it by undocumented stored procedure available in Microsoft SQL Server 2005, Sp_MSforEachDB.

Let us see how I did it.

EXEC sp_MSforeachdb

'if ''?'' <> ''tempdb'' and ''?'' <> ''master''

begin

USE ? SELECT name,db_name() databasename FROM sys.sysusers

WHERE name = ''Guest'' ORDER BY name

end

'

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com