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.
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
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'
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
IT Security, MySQL, Perl, SQL Server, and Windows technologies.