Find db's that has the user

Question

Find db's that has the user

Tara-1044200

SSCoach

Points: 15951
More actions
July 11, 2012 at 2:22 pm

#263683

How can i find list databases that has user "Domain\JSmith" ?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Query sys.database_principals in each database for that user.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lowell SSC Guru Points: 323518 More actions · Answer 2

and a code example of what Jason was talking about:

sp_msForEachDB 'select ''?'' AS DatabaseName,

name AS UserName

FROM [?].sys.database_principals

WHERE name = ''Domain\JSmith'' '

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Tara-1044200 SSCoach Points: 15951 More actions · Answer 3

i have more than 400 dbs so i cant use spmsforeachdb as it wonts display everything in the result, is there a way i can just get the list of only those db that has this user?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Tara-1044200 (7/11/2012)
i have more than 400 dbs so i cant use spmsforeachdb as it wonts display everything in the result, is there a way i can just get the list of only those db that has this user?

Sure you can use msforeachdb, it just takes some tweaking.

CREATE TABLE #UserExists (DatabaseName VARCHAR(128), UserName VARCHAR(128));

GO

EXECUTE sp_msForEachDB 'Insert Into #UserExists (DatabaseName, UserName)

select ''?'' AS DatabaseName,

name AS UserName

FROM [?].sys.database_principals

WHERE name = ''Domain\JSmith'' ';

SELECT DatabaseName, UserName

FROM #UserExists;

DROP TABLE #UserExists;

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events