SQL Server - Query to list users, roles and databases ?

Is there an easy way to query to find out what users have what roles
in what databases?

Ex:

Login     smith is dbreader and dbwriter in db1
smith is dbreader in db2

etc..

Thanks

Check out the following:
sp_helplogin
sp_helpuser

RLF

I did and it did not help.  Any other ideas?

Thanks,

Why didn't it help?  What were you expecting?  What did you get?

sp_helplogins (sorry for the typo without the 's') returns two result sets.
The second is:
LoginName, DatabaseName, UserName, UserOrAlias
If the UserName is a Role, then UserOrAlias = 'MemberOf'

sp_helpuser within a database returns:
UserName, GroupName(This is the rolename), LoginName, etc.

It is true that the result sets are not formatted like your string below,
but they should have your answers.

RLF

Sorry, but I am looking for a single sql statement that can give me
that output.  Are there a couple of system tables that have this info
that I could join?

Then it looks like you should read the code in sp_helplogins and create your
own version.  As you will see, the tables you need for role membership are
specific to each database.

RLF

To find out the number of users created, you can fire this query.



SELECT * FROM dba_users WHERE username LIKE 'xxx%' ORDER BY CREATED DESC;



/* if you looking at a specific user sequence name*/



SELECT * FROM dba_users ORDER BY CREATED DESC



/*list out all */





**Note: need to have grant privileges

Hey,



Here is a blog post that details a query that you can use to find out the database and server roles for which a given login is a member:



http://nickstips.wordpress.com/2010/10/07/sql-get-login-database-and-server-role-membership/



Hope it helps!



Nick