Getting All Users

I saw someone that wanted to get all users from all databases on their instance. Seems like that ought to be simple, right?

The user wanted to use sp_MSforeachdb to query users, but wanted a single result set for all databases.  Why you need this, I’m not sure. I guess some auditing report. Or maybe looking to clean up and remove unnecessary users?

In any case, this turned out to be easy with a caveat. It’s not in one single statement..

DECLARE @MyUsers TABLE ( dbname VARCHAR(200), principalname VARCHAR(200), principalSID VARBINARY(MAX))
  INSERT @MyUsers
EXEC sp_MSforeachdb 'select ''?'', name, sid from [?].sys.sysusers'

SELECT top 1000
  *
  FROM @myusers

Use the ? in the query to get the users from every db. Then store this in a table that you can pull back all the data.

This worked for me, though since I have some database names with a hypen (-) in them, I needed the brackets to get this to work.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.