Master of None

Sometimes you need to find login information. Looking at just the logins on an instance will not allow you to find how a user is connecting. A login maybe nested in an Active Directory group or a login locally to the instance.
SQL server has various commands to provide “HELP”. They can be used to look up or find characteristics of all sorts things like information about users, databases, indexing and much more. This post will look at sys.xp_logininfo and sys.helplogins. SQL 2014 and SQL 2016 have new procedure sys.sp_helpntgroup to further examine rights.

A good practice is to create specific AD groups and add and remove logins from those groups. You standardize the login’s security and access. It also makes it easier to remove access.

The below query uses a parameter to return login information from these procedures.

/*
Find User login access by AD group and SQL Login
01/21/2016
jackdonnell.com
*/
USE master
GO
SET NOCOUNT ON
GO

DECLARE @loginname VARCHAR(60)
SET @loginname = 'LOCAL\ADUserName'
--------------------------------------------------
SELECT SERVERPROPERTY('ServerName') [Instance]

-- Check AD Group Access
EXEC [sys].[xp_logininfo] @loginname ,'all'
-- SQL Login Access
EXEC [sys].[sp_helplogins] @loginname
GO

Tiny URL for this post:
 Copy