http://www.sqlservercentral.com/blogs/brian_kelley/2006/04/29/more-on-xp_logininfo/

Printed 2014/10/20 08:09AM

More on xp_logininfo

2006/04/29

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.



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.