April 5, 2006 at 12:46 pm
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
April 6, 2006 at 9:03 am
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!
April 7, 2006 at 8:27 am
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
@kbriankelley
June 12, 2006 at 12:14 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply