July 25, 2008 at 2:22 am
I want to keep track of all users on a sql database, want to know if it is possible to select all the users on the database including those in the NT Groups.
Currently I have to count the users in the NT Groups then add them to the amount of users on the database. Sure there must be an easier way to do this.
July 25, 2008 at 3:01 am
Hi,
The sysusers table holds all the users (SQL and windows) information. It also includes the roles in that database. If you want to exclude the roles, may be you can use a condition in issqlrole,isapprole.
If I have understood your question correctly, this should work.
[font="Verdana"]Renuka__[/font]
July 25, 2008 at 3:23 am
Thanks so much for your reply, yes the sysusers has all the users but it shows the Windows NT group name as 1 User.Not the users that are part of the Windows NT GROUP. So if their are 5 users in that group and if you do a count on the sysusers table it will pick up 1 user(the NT GROUP) and not 5 users which are included in that group
Hope this is understandable
July 25, 2008 at 3:29 am
Hi
do the nt group users have separate logins for each one of them? if so you can query the sys.logins table.
"Keep Trying"
July 25, 2008 at 3:46 am
The sysxlogins table returns the same example
CORPORATE\FER06137
CORPORATE\HEI01720
corporate\hib05552
CORPORATE\hla06441
CORPORATE\Hot Products Group
CORPORATE\jac06909
CORPORATE\jag06779
CORPORATE\jor23611
It picks up 8 Users on the database, but what if their are 10 users in the CORPORATE\Hot Products Group. This will mean that their are actually 17 Users that have access to this database
July 25, 2008 at 4:58 am
Hi,
Hope this helps your requirement.
xp_logininfo 'Windows Group Name','members'
[font="Verdana"]Renuka__[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply