When using windows login for access to SQL instances, you can add Active Directory groups as logins and apply the permissions. However, this means that the individual users access is hidden from the DBA. This can cause confusion as a user can be a member of more than one group, and overlapping permissions can prevent users from working (for example: an explicit deny on an object in one permission set will always override an allow permissions.)
You could then have access to Active Directory, but chances are your Domain Admins will refuse access, and quite rightly, because I sure wouldn’t want someone poking around the security for my SQL Instances, let alone domain wide security! Fortunately, there is a solution for us DBA’s. By using General Extended Stored Procedures, which provide an interface from an instance of SQL Server to external programs for various maintenance activities we can access Active Directory for all of the info we need.
xp_logininfo returns information about Windows users and Windows groups from Active Directory. To set the permissions on the instance to run xp_logininfo you must either set
- membership in the sysadmin fixed server role
- membership in the public fixed database role in the master database with EXECUTE permission granted.
It’s important to note that xp_logininfo only returns information from Active Director global groups, not universal groups.
Let’s start simple: Running the below query will only return one row, the first permission path found.
EXEC xp_logininfo 'Domain\User'
(I got to back track a little bit but it’s worth it: if @acctname is not specified all Windows groups and Windows users that have been explicitly granted login permission are returned. If you specify an account, you must make sure that @acctname is fully qualified.)
You can also execute like so:
EXEC master..xp_logininfo @acctname = 'Domain\User' go
However, the user’s account can have multiple permission paths, causing potential conflicts. To see all of the permission paths for a user, specify the value for option as ‘all’. For instance:
EXEC xp_logininfo 'Domain\User', 'all'
EXEC master..xp_logininfo @acctname = 'Domain\User',@option = 'all' go
You can also view the members of a particular group by setting the @option as ‘member’. The @option is NULL by default.
EXEC xp_logininfo 'DOMAIN\GlobalGroup', 'members'
EXEC master..xp_logininfo @acctname = 'DOMAIN\GlobalGroup',@option = 'members' go
I have written a little script that can take either member or user and you can view their full permissions.
DECLARE @WindowsLogin sysname DECLARE @LoginInfo TABLE ( [Account Name] sysname, [Type] char(8), [Privilege] char(9), [Mapped Login Name] sysname, [Permission Path] sysname NULL ) --set user you want to run this for SET @WindowsLogin = 'Domain\User' -- or 'Domain\GlobalGroup' --get user/group info INSERT INTO @LoginInfo EXEC xp_logininfo @WindowsLogin, 'all' IF EXISTS (SELECT 1 FROM @LoginInfo WHERE [Type] = 'group') INSERT INTO @LoginInfo EXEC xp_logininfo @WindowsLogin, 'members' --run to get output SELECT * FROM @LoginInfoFollow @rPh0enix