Users by Database

A script that finds which databases contain a given user by searching sysusers across the whole server.

Published Dec 4, 2007

Which databases does a given user exist in? This script walks every online database on the server and searches sysusers for names matching @SearchName, collecting the results into one list with the server name. Change the search string at the top before running.

SET NOCOUNT ON;

CREATE TABLE #Users
(
	DBName varchar(128),
	Username varchar(128)
)

DECLARE @DBName VarChar(128)
DECLARE @SQL VarChar(1000)
DECLARE @SearchName VarChar(128) SET @SearchName = '<WhoAreYouLokingFor>'

DECLARE dbs CURSOR FOR
	SELECT name FROM sys.databases WHERE state_desc = 'ONLINE'
OPEN dbs

FETCH FROM dbs INTO @DBName
WHILE(@@FETCH_STATUS = 0)
BEGIN--IF
	set @SQL = 'INSERT INTO #Users SELECT ''' + @DBName + ''', name FROM [' + @DBName + ']..sysusers WHERE LOWER(name) LIKE ''%' + @SearchName + '%'''
	exec(@SQL)
	fetch from dbs into @DBName
END--IF

CLOSE dbs DEALLOCATE dbs

SELECT @@servername, * FROM #Users

DROP TABLE #Users