how do i find out what group a user is using to access server?

Question

Post reply

how do i find out what group a user is using to access server?

winston Smith

SSCoach

Points: 19500
More actions
June 24, 2009 at 4:37 am

#206388

We have a lot of users and very few have direct access to the databases. instead there are a number of groups created on the network via active directory and the groups have permissions on the database.
Users are then assigned to these groups.
Occasionally we find a user on the box that shouldnt be there. is there any way to find out through which group this user has gained access to the box, besides physically looking in each group and checking for the username?

Viewing 9 posts - 1 through 8 (of 8 total)

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

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

Extended stored procedure xp_logininfo can list the Active Directory groups of an account. An example execution is:

exec master.dbo.xp_logininfo 'IBM-5C01DE43A36\cfederl' ,'all'

From Books OnLine:

If account_name and all are specified, all permission paths for the Windows user or group are returned. If account_name is a member of multiple groups, all of which have been granted access to SQL Server, multiple rows are returned. The admin privilege rows are returned before the user privilege rows, and within a privilege level rows are returned in the order in which the corresponding SQL Server logins were created. [\quote]

SQL = Scarcely Qualifies as a Language

winston Smith SSCoach Points: 19500 More actions · Answer 2

winston Smith

SSCoach

Points: 19500

June 24, 2009 at 6:13 am

#1014574

awesome. exactly what i needed. thanks a mil.

winston Smith SSCoach Points: 19500 More actions · Answer 3

Im running the query and it works for the most part.

when i query groups like BUILTIN\Administrators it tells me all the groups in that.

But when i query active Directory groups, even if they are added as a login to the instance, i still get an error:

exec master.dbo.xp_logininfo 'DOMAIN\GROUP' ,'members'

Msg 15404, Level 16, State 4, Procedure xp_logininfo, Line 42

Could not obtain information about Windows NT group/user 'DOMAIN\GROUP', error code 0x35.

I cant find anything specific to xp_logininfo and error 0x35, but in google it seems to say i dont have access to the specified file or resource. does this mean i need additional permissions in active directory to be able to query it?

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

xp_logininfo invokes Windows API and does not communicate directly with Active Directory. Message 15404 is a catch all message meaning either permission denied or resource not available. Since you are able to run versus the local administrators group, you have sufficient SQL Server permissions.

One cause of permission denied is if the domain of the SQL Server versus the domain of the login are different and there is no trust between the two domains.

Another cause is that the login domain is not running or not accessable.

I have also seen this occur when the Window policy denies needed rights to perform some operations.

Take as look at the Windows Event log on the server to see if there is any information.

SQL = Scarcely Qualifies as a Language

Paul White SSC Guru Points: 150467 More actions · Answer 5

winston Smith (6/24/2009)
Im running the query and it works for the most part.
when i query groups like BUILTIN\Administrators it tells me all the groups in that.

Are you logged in with an account that is a local Administrator, a domain administrator or both?

Sounds like local only?

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi