adding up Number of users on a sql database including those in NT GROUPS

Question

adding up Number of users on a sql database including those in NT GROUPS

SQL_Student

SSCrazy

Points: 2536
More actions
July 25, 2008 at 2:22 am

#194625

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Renuka__ Hall of Fame Points: 3656 More actions · Answer 1

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]

SQL_Student SSCrazy Points: 2536 More actions · Answer 2

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

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"

SQL_Student SSCrazy Points: 2536 More actions · Answer 4

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

Renuka__ Hall of Fame Points: 3656 More actions · Answer 5

Hi,

Hope this helps your requirement.

xp_logininfo 'Windows Group Name','members'

[font="Verdana"]Renuka__[/font]