Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Query to list users, roles and databases ?

124 views
Skip to first unread message

bringm...@gmail.com

unread,
Apr 13, 2007, 11:53:11 AM4/13/07
to
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

unread,
Apr 13, 2007, 12:56:00 PM4/13/07
to
Check out the following:
sp_helplogin
sp_helpuser

RLF
<bringm...@gmail.com> wrote in message
news:1176479591.9...@d57g2000hsg.googlegroups.com...

bringm...@gmail.com

unread,
Apr 13, 2007, 2:26:58 PM4/13/07
to

I did and it didn't help. Any other ideas?

Thanks,

On Apr 13, 12:56 pm, "Russell Fields" <russellfie...@nomail.com>
wrote:


> Check out the following:
> sp_helplogin
> sp_helpuser
>

> RLF<bringmewa...@gmail.com> wrote in message

Russell Fields

unread,
Apr 13, 2007, 2:52:01 PM4/13/07
to
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
<bringm...@gmail.com> wrote in message

news:1176488818.9...@w1g2000hsg.googlegroups.com...

bringm...@gmail.com

unread,
Apr 13, 2007, 3:07:32 PM4/13/07
to
Sorry, but I'm 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

unread,
Apr 13, 2007, 3:20:44 PM4/13/07
to
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
<bringm...@gmail.com> wrote in message

news:1176491252.8...@q75g2000hsh.googlegroups.com...

bhavnalakshman

unread,
Jan 22, 2010, 12:24:30 AM1/22/10
to

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

bringmewate wrote:

Sorry, but I'm looking for a single sql statement that can give methat output.

13-Apr-07

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?

Previous Posts In This Thread:

On Friday, April 13, 2007 11:53 AM
bringmewate wrote:

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

On Friday, April 13, 2007 12:56 PM
Russell Fields wrote:

Re: Query to list users, roles and databases ?


Check out the following:
sp_helplogin
sp_helpuser

RLF

On Friday, April 13, 2007 2:26 PM
bringmewate wrote:

I did and it didn't help. Any other ideas?

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

Thanks,

wrote:

On Friday, April 13, 2007 2:52 PM
Russell Fields wrote:

Why didn't it help? What were you expecting? What did you get?
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
<bringm...@gmail.com> wrote in message
news:1176488818.9...@w1g2000hsg.googlegroups.com...

On Friday, April 13, 2007 3:07 PM
bringmewate wrote:

Sorry, but I'm looking for a single sql statement that can give methat output.

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?

On Friday, April 13, 2007 3:20 PM
Russell Fields wrote:

Then it looks like you should read the code in sp_helplogins and create your
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


Submitted via EggHeadCafe - Software Developer Portal of Choice
Build a Script-Only ASP.NET 2.0 Data Editing Page
http://www.eggheadcafe.com/tutorials/aspnet/274279bd-d101-4925-9857-246a66a85014/build-a-scriptonly-aspn.aspx

Kurt

unread,
Jan 24, 2010, 5:37:01 AM1/24/10
to
thanks bhvna

Nick Olsen

unread,
Oct 8, 2010, 7:26:53 PM10/8/10
to
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

> On Friday, April 13, 2007 11:53 AM bringmewate wrote:


>> On Friday, April 13, 2007 12:56 PM Russell Fields wrote:

>> Check out the following:
>> sp_helplogin
>> sp_helpuser
>>
>> RLF


>>> On Friday, April 13, 2007 2:26 PM bringmewate wrote:

>>> I did and it did not help. Any other ideas?
>>>
>>> Thanks,
>>>
>>> wrote:


>>>> On Friday, April 13, 2007 2:52 PM Russell Fields wrote:

>>>> 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
>>>> <bringm...@gmail.com> wrote in message
>>>> news:1176488818.9...@w1g2000hsg.googlegroups.com...


>>>>> On Friday, April 13, 2007 3:07 PM bringmewate wrote:

>>>>> 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?


>>>>>> On Friday, April 13, 2007 3:20 PM Russell Fields wrote:

>>>>>> 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


>>>>>>> On Friday, January 22, 2010 12:24 AM Bhavna Lakshman wrote:

>>>>>>> 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

>>>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice

>>>>>>> Autocorrelation method in C# for signal analysis
>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/d39ee525-a402-46cf-9989-72b7256f76b1/autocorrelation-method-in-c-for-signal-analysis.aspx

0 new messages