{"id":583,"date":"2016-01-21T13:31:42","date_gmt":"2016-01-21T19:31:42","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=583"},"modified":"2016-01-21T13:32:03","modified_gmt":"2016-01-21T19:32:03","slug":"i-am-only-here-to-help-sys-xp_logininfo-and-sys-helplogins","status":"publish","type":"post","link":"https:\/\/jackdonnell.com\/?p=583","title":{"rendered":"I am Only Here to Help &#8211; sys.xp_logininfo and sys.helplogins"},"content":{"rendered":"<p>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 <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/windows\/desktop\/aa746492(v=vs.85).aspx\" target=\"_blank\">Active Directory<\/a> group or a login locally to the instance.<br \/>\nSQL server has various commands to provide &#8220;HELP&#8221;. 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 <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms190369.aspx\" target=\"_blank\">sys.xp_logininfo<\/a> and <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms190304.aspx\" target=\"_blank\">sys.helplogins<\/a>. SQL 2014 and SQL 2016 have new procedure <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms173872.aspx\" target=\"_blank\">sys.sp_helpntgroup<\/a> to further examine rights. <\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-638\" src=\"https:\/\/jackdonnell.com\/wp-content\/uploads\/2016\/01\/securitylookup.gif\" alt=\"securitylookup\" width=\"721\" height=\"321\" \/><\/p>\n<p>A good practice is to create specific AD groups and add and remove logins from those groups. You standardize the login&#8217;s security and access. It also makes it easier to remove access.<br \/>\n<!--more--><\/p>\n<p>The below query uses a parameter to return login information from these procedures.<\/p>\n<pre lang=\"tsql\">\/*\r\nFind User login access by AD group and SQL Login\r\n01\/21\/2016\r\njackdonnell.com\r\n*\/\r\nUSE master\r\nGO\r\nSET NOCOUNT ON\r\nGO\r\n\r\nDECLARE @loginname VARCHAR(60)\r\nSET @loginname = 'LOCAL\\ADUserName'\r\n--------------------------------------------------\r\nSELECT SERVERPROPERTY('ServerName') [Instance]\r\n\r\n-- Check AD Group Access\r\nEXEC [sys].[xp_logininfo] @loginname ,'all'\r\n-- SQL Login Access\r\nEXEC [sys].[sp_helplogins] @loginname\r\nGO<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>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 &hellip;<\/p>\n<p class=\"read-more\"><a href=\"https:\/\/jackdonnell.com\/?p=583\">Read more &raquo;<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"footnotes":""},"categories":[273,266,22],"tags":[284,405,406],"class_list":["post-583","post","type-post","status-publish","format-standard","hentry","category-administration","category-dba","category-sql-server","tag-serverproperty","tag-sp_helplogins","tag-xp_logininfo"],"_links":{"self":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/583","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=583"}],"version-history":[{"count":10,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/583\/revisions"}],"predecessor-version":[{"id":648,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/583\/revisions\/648"}],"wp:attachment":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=583"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=583"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=583"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}