Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to identify GROUPS in database roles Expand / Collapse
Author
Message
Posted Wednesday, April 5, 2006 12:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 9, 2009 10:10 PM
Points: 39, Visits: 12

Hi

I am writing these scripts to prepare report that shows all the users that have dbo rights on a given database.

By using sp_helprolemember 'db_owner' I can get the required list. But some of these members are Local or Domain groups. Under such situations, I have to show all the logins within these groups (iteratively if required). I can do that also by writing a VB script to read from Active directory.

But the main problem I am having here is how to identify if a dbo role member is a group or just a login?

Is there some table/view/stored proc that can give us this info?

Thanks

Prasad

Post #271372
Posted Thursday, April 6, 2006 9:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 9, 2009 10:10 PM
Points: 39, Visits: 12

Nevermind. I got it.

I am now using sp_helpuser, so that I can get both database user and login.

After that I can use isntgroup column in sysusers table to find which one of these account are actually groups.

Thanks!

Post #271602
Posted Friday, April 7, 2006 8:27 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
I hadn't seen your post when I wrote the blog entry, so it's a nice coincidence. If you want to get everything in one query, you can query the sysusers and syslogins tables directly with a join.

Determining if it's a SQL login, Windows Login, or Windows group


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #271930
Posted Monday, June 12, 2006 12:14 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 1:49 PM
Points: 436, Visits: 590

If you want to get the accounts that are in those groups, you can use the xp_logininfo command.  Use it in the following manner:

EXEC master..xp_logininfo 'DOMAIN\Group', @option = 'members'

Good luck!

 



----------------------------------------------------------

01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011
Post #286800
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse