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