http://www.sqlservercentral.com/blogs/brian_kelley/2006/04/29/more-on-xp_logininfo/ Printed 2014/03/09 01:55PM
More on xp_logininfo
2006/04/29In 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