I am Only Here to Help – sys.xp_logininfo and sys.helplogins

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.
Read more »

Quick Server Info – sys.dm_os_sys_info and SERVERPROPERTY ()

I needed a quick query to get some server information using  sys.dm_os_sys_info and SERVERPROPERTY ( propertyname ) .  It is budget time so folks want to know some specifics about some of the current infrastructure. This is a nice way to provide a high level look at you infrastructure. Tested on SQL 2005 and SQL 2008 R2.

The SQLOS Team  has a neat article about changes in 2012 for the DMV.

Read more »