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

Asked By bringmewate on 13-Apr-07 11:53 AM
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




Russell Fields replied on 13-Apr-07 12:56 PM
Check out the following:
sp_helplogin
sp_helpuser

RLF
bringmewate replied on 13-Apr-07 02:26 PM
I did and it did not help.  Any other ideas?

Thanks,
Russell Fields replied on 13-Apr-07 02:52 PM
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
bringmewate replied on 13-Apr-07 03:07 PM
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?
Russell Fields replied on 13-Apr-07 03:20 PM
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
Bhavna Lakshman replied to bringmewate on 22-Jan-10 12:24 AM
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
Nick Olsen replied to bringmewate on 08-Oct-10 07:26 PM
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