In this post,

I talked about using xp_logininfo in order to get the permission paths

for a particular Windows account. If you've never used xp_logininfo, it

can be a valuable tool when you're trying to figure all the ways a

particular user account maps into SQL Server. After all, a Windows user

could be a member of multiple Windows groups and two or more of those

groups may have been granted the ability to logon to SQL Server. Let's

talk a look at xp_logininfo to see how to use it.

Usage:

There are three parameters: @acctname, @option, and @privilege (ignore SQL Server 2000's Books Online entry

where the syntax is given as @privelege, as that is incorrect). The

last parameter, @privilege, is an output parameter but I'll admit that

I've never used it. It's supposed to return the privilege of the

Windows account specified in @acctname, but if I specify anything other

than 'Not wanted' as a value for it, SQL Server generates the following

error:

Msg 15600, Level 15, State 1, Procedure xp_logininfo, Line 16

An invalid parameter or option was specified for procedure 'sys.xp_logininfo'.

If I specify 'Not wanted', that's what I get back. I haven't found that

last parameter particularly useful as the privilege for a given SQL

Server login is always given as part of the result set. The first

parameter, @acctname is self-explanatory. If that's specified, it'll

return rows back based on what SQL Server knows of the login. For

instance, this will return information about the user Domain\User:

EXEC xp_logininfo 'Domain\User'

By default, only one row will be returned. However, in some cases a

given user account can have multiple permission paths. To see them all,

specify a value for option: 'all'. For instance:

EXEC xp_logininfo 'Domain\User', 'all'

If you want to see the members of a particular Windows group instead,

specify the group name for the @acctname parameter and the value

'members' for the @option parameter. A good example of seeing who all

is in BUILTIN\Administrators is the following:

EXEC xp_logininfo 'BUILTIN\Administrators', 'members'

Limitations:

In testing on SQL Server 2000, I have found limitations with

xp_logininfo, but they tend to deal with nested groups and domain local

groups. For instance, Domain\Domain Admins is added by default to the

local Administrators group whenever a server is added to a domain.

Doing a member listing on BUILTIN\Administrators I can see

Domain\Domain Admins just fine (where the first Domain represents my

domain). However, if I try and execute xp_logininfo 'Domain\Domain

Admins', I won't get anything back unless Domain\Domain Admins is also

a valid login for SQL Server.

The domain local group is typically used when you want to create a

security group to contain a global group from another domain. For

instance, I want to make Domain2\Domain Admins a member of a domain

local group Domain\Other Admins. I can then use Domain\Other Admins

anywhere I want to grant security without issue. However, if I were to

run an xp_logininfo against either group, I won't get anything back,

just like with the nested group above. Therefore, xp_logininfo doesn't

help me a whole lot here, either.

If I want to get around these two limitations, unfortunately I am forced

to run scripts against the domain. So while xp_logininfo can be useful

for individual user accounts, I wouldn't use it against most groups, at

least not in SQL Server 2000. I need to test it on SQL Server 2005 on a

domain to determine if the behavior remains the same. Once I do, I'll

post to the blog. Therefore, these limitations may be fixed in the

newest version. However, as of SQL Server 2000 SP4, these limitations

were present.