Overview
In this real world scenario I had to determine whether certain developers were part of a Windows group using T-SQL. The Windows group in question was an Active Directory Group and I do not have the necessary privileges to query the Active Directory server. I used an alternative approach using the system stored procedure xp_logininfo. Note – per books online: In previous versions of SQL Server permissions to run this could be granted to users. In SQL Server 2005, this can only be run by a user with membership in the db_owner fixed database role in the master database or the sysadmin fixed server role.
.Luckily, at the present time, I am an administrator on the new server. At some point in the near future, my privileges will be reduced.
How to Use the Stored Procedure
There are at least three uses for xp_logininfo that are tremendously valuable.
1) List all members in built-in Windows groups. For example the following T-SQL returns all of the members in the ‘BUILTIN\Administrators’ group:
use master
EXEC xp_logininfo 'BUILTIN\Administrators', 'members'
2) List all members in an Active Directory group. Assume that there is an Active Directory domain called NS and in the NS domain there is a group called ‘Web Admins’. The following T-SQL returns all of the members of the Web Admins group within the NS domain:
use master
EXEC xp_logininfo 'NS\Web Admins', 'members'
3) Determine if an active directory user is a member of any Windows groups with access to SQL Server. In the example below, the code check to see if user ‘abcde’ is a member of any SQL Server group with server access. If the user has access to SQL Server – the account name, type, privilege, mapped login name, and permission path are returned. If a user does not have access, an error is thrown.
(Example of a user with access to SQL Server)
EXEC xp_logininfo 'DS\abcde'
Results returned by query
|
account name
|
type
|
privilege
|
mapped login name
|
permission path
|
|
NS\abcde
|
user
|
admin
|
NS\abcde
|
BUILTIN\Administrators
|
(Example of a user with no access to SQL Server)
EXEC xp_logininfo 'DS\abcde'
Results returned by query
Msg 15404, Level 16, State 11, Procedure xp_logininfo, Line 62
Could not obtain information about Windows NT group/user 'DS\abced', error code 0x534.
