Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.

More on xp_logininfo

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.


Comments

Posted by ric.murri on 13 November 2014

It appears that the limitations are still present in SQL Server 2012 (at least in the nested group having to have a valid login for SQL Server)

Leave a Comment

Please register or log in to leave a comment.