Users for all Databases

[mossbs]

Hi Guys,

Is there anyway to write a script that wil produce table showing all the Users from sysusers for every database on the server?

I know of the undocumented sp_MSforeachdb - but this produces a table for each... what i was after was ONE table ... something like....

DbName | User | CreateDate | UpdateDate



...is this at all possible?

Cheers,

Dan

 

[simian336]

Actually SP_MSForEachDB is for each database. SP_MSForEachTable is for each table.

Simi

 

[simian336]

look at

http://www.kodyaz.com/articles/sp_Msforeachdb-example-list-all-database-files.aspx

Simi

 

[gmmastros]

simian,

I think mossbs meant that he can use sp_msforeachdb to run a query in each database, but each call to sp_msforeachdb returns a separate recordset. Sounds like he wants to combine all the recordsets in to one.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mossbs]

Cheers for replies,

As George said, i am after this producing just one recordset rather than a seperate one for each database.

Basically i'm doing an audit on all users/logins on all of our sql servers, and with each instance having upward of 10 databases on and there being upward of 15 sql servers... i wouldn't mind having a quicker way of doing this rather than doing it for each database then having to collate them all at the end!

cheers all!

 

[RiverGuy]

This should work

CREATE TABLE ##Users
(
DB VARCHAR(100),
UserName VARCHAR(100),
CreateDate DATETIME,
UpdateDate DATETIME
)

EXEC master.sys.sp_MSforeachdb 'USE [?] INSERT INTO ##Users SELECT DB_NAME() AS DB, [name], createdate, updatedate FROM sys.sysusers'

SELECT * FROM ##Users

DROP TABLE ##Users

 

[markros]

You may find this blog post
How to get information for all databases without a loop helpful in solving this problem.

PluralSight Learning Library