{"id":2329,"date":"2014-04-07T11:00:09","date_gmt":"2014-04-07T15:00:09","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2329"},"modified":"2014-11-13T11:59:23","modified_gmt":"2014-11-13T16:59:23","slug":"loginproperty","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2329\/loginproperty\/","title":{"rendered":"LOGINPROPERTY \u2013 Getting to know your SQL Logins even more"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nYesterday we discovered the sys.sql_logins<\/span><\/a> catalog view and saw that it returns additional information on SQL Logins that is not included in the sys.server_principals<\/span> catalog view.
\nHowever, there is more information on SQL Logins available than that catalog view reveals. To get to that additional information, you can use the LOGINPROPERTY<\/span> catalog function.\n<\/p>\n

LOGINPROPERTY Example<\/h3>\n

\nFor today's examples, we are going to use the same login we created yesterday:\n<\/p>\n

\n[sql]\nCREATE LOGIN ASqlLogin WITH PASSWORD='********';
\n[\/sql]\n<\/div>\n

\nThe LOGINPROPERTY<\/span> catalog function takes two parameters. The first one, login_name<\/span> is the name of the SQL Login you want to check the properties on. The second one is the property_name<\/span> and it specifies which property you are after.\n<\/p>\n

\nLet us look at an example:\n<\/p>\n

\n[sql]\nSELECT LOGINPROPERTY('ASqlLogin','BadPasswordCount') AS BadPasswordCount,
\n LOGINPROPERTY('ASqlLogin','IsLocked') AS IsLocked;
\n[\/sql]\n<\/div>\n

\nThose two properties let us know how many login attempts for this SQL Login have been recorded and if the account has been locked because of it. An example output is shown below:\n<\/p>\n

\n\"LOGINPROPERTY<\/a>\n<\/p>\n

\nThere are in total thirteen properties that you can check with this catalog function. To make using that function a little easier, I created a table valued function that returns all properties for a given SQL Login name:\n<\/p>\n

\n[sql]\nIF OBJECT_ID('dbo.login_properties') IS NOT NULL DROP FUNCTION dbo.login_properties;
\nGO
\nCREATE FUNCTION dbo.login_properties(@login_name NVARCHAR(MAX))
\nRETURNS TABLE
\nAS
\nRETURN SELECT
\nLOGINPROPERTY(@login_name,'BadPasswordCount') AS [BadPasswordCount],
\nLOGINPROPERTY(@login_name,'BadPasswordTime') AS [BadPasswordTime],
\nLOGINPROPERTY(@login_name,'DaysUntilExpiration') AS [DaysUntilExpiration],
\nLOGINPROPERTY(@login_name,'DefaultDatabase') AS [DefaultDatabase],
\nLOGINPROPERTY(@login_name,'DefaultLanguage') AS [DefaultLanguage],
\nLOGINPROPERTY(@login_name,'HistoryLength') AS [HistoryLength],
\nLOGINPROPERTY(@login_name,'IsExpired') AS [IsExpired],
\nLOGINPROPERTY(@login_name,'IsLocked') AS [IsLocked],
\nLOGINPROPERTY(@login_name,'IsMustChange') AS [IsMustChange],
\nLOGINPROPERTY(@login_name,'LockoutTime') AS [LockoutTime],
\nLOGINPROPERTY(@login_name,'PasswordHash') AS [PasswordHash],
\nLOGINPROPERTY(@login_name,'PasswordLastSetTime') AS [PasswordLastSetTime],
\nLOGINPROPERTY(@login_name,'PasswordHashAlgorithm') AS [PasswordHashAlgorithm];
\n[\/sql]\n<\/div>\n

\nI usually place that function in the master<\/span> database as the logins are stored there too, but it works in other databases too. An explanation for each of the properties you can find in BOL<\/a>.\n<\/p>\n

\nYou can use the dbo.login_properties<\/span> function for example by cross applying it to the sys.sql_logins<\/span> catalog view like this:\n<\/p>\n

\n[sql]\nSELECT SL.name,LP.*
\n FROM sys.sql_logins AS SL
\n CROSS APPLY dbo.login_properties(SL.name) AS LP;
\n[\/sql]\n<\/div>\n

Summary<\/h3>\n

\nThe sys.sql_logins<\/span> catalog view does not return all authentication related properties for a SQL Login. The LOGINPROPERTY<\/span> catalog function closes that gap by providing an additional twelve properties. This article introduced the dbo.login_properties<\/span> table valued function that returns all LOGINPROPERTY<\/span> values for a given SQL Login.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

LOGINPROPERTY is a SQL Server catalog function that can be used to query a total of twelve properties for SQL Logins not even revealed by the sys.sql_logins catalog view. Read on to find out how to use LOGINPROPERTY and which properties it can return.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[118,5,34,119],"tags":[157,38,58,56,156,15],"class_list":["post-2329","post","type-post","status-publish","format-standard","hentry","category-dmvs-cvs","category-general","category-security","category-security-dmvs-cvs","tag-loginproperty","tag-security-2","tag-security-management","tag-server-principals","tag-sql-login","tag-sql-server"],"acf":[],"yoast_head":"\nLOGINPROPERTY \u2013 Getting to know your SQL Logins even more - sqlity.net<\/title>\n<meta name=\"description\" content=\"Find out how LOGINPROPERTY can be used to get additional properties for SQL Logins in SQL Server that not even the sys.sql-logins catalog view reveals.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/2329\/loginproperty\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"LOGINPROPERTY \u2013 Getting to know your SQL Logins even more - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Find out how LOGINPROPERTY can be used to get additional properties for SQL Logins in SQL Server that not even the sys.sql-logins catalog view reveals.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2329\/loginproperty\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2014-04-07T15:00:09+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T16:59:23+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/LOGINPROPERTY_in_action.jpg\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2329\\\/loginproperty\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2329\\\/loginproperty\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"LOGINPROPERTY \u2013 Getting to know your SQL Logins even more\",\"datePublished\":\"2014-04-07T15:00:09+00:00\",\"dateModified\":\"2014-11-13T16:59:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2329\\\/loginproperty\\\/\"},\"wordCount\":488,\"commentCount\":1,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2329\\\/loginproperty\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/04\\\/LOGINPROPERTY_in_action.jpg\",\"keywords\":[\"LOGINPROPERTY\",\"security\",\"security management\",\"server principals\",\"SQL Login\",\"SQL Server\"],\"articleSection\":[\"DMVs & CVs\",\"General\",\"Security\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2329\\\/loginproperty\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2329\\\/loginproperty\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2329\\\/loginproperty\\\/\",\"name\":\"LOGINPROPERTY \u2013 Getting to know your SQL Logins even more - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2329\\\/loginproperty\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2329\\\/loginproperty\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/04\\\/LOGINPROPERTY_in_action.jpg\",\"datePublished\":\"2014-04-07T15:00:09+00:00\",\"dateModified\":\"2014-11-13T16:59:23+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Find out how LOGINPROPERTY can be used to get additional properties for SQL Logins in SQL Server that not even the sys.sql-logins catalog view reveals.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2329\\\/loginproperty\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2329\\\/loginproperty\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2329\\\/loginproperty\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/04\\\/LOGINPROPERTY_in_action.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/04\\\/LOGINPROPERTY_in_action.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2329\\\/loginproperty\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"LOGINPROPERTY \u2013 Getting to know your SQL Logins even more\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"LOGINPROPERTY \u2013 Getting to know your SQL Logins even more - sqlity.net","description":"Find out how LOGINPROPERTY can be used to get additional properties for SQL Logins in SQL Server that not even the sys.sql-logins catalog view reveals.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sqlity.net\/en\/2329\/loginproperty\/","og_locale":"en_US","og_type":"article","og_title":"LOGINPROPERTY \u2013 Getting to know your SQL Logins even more - sqlity.net","og_description":"Find out how LOGINPROPERTY can be used to get additional properties for SQL Logins in SQL Server that not even the sys.sql-logins catalog view reveals.","og_url":"https:\/\/sqlity.net\/en\/2329\/loginproperty\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-04-07T15:00:09+00:00","article_modified_time":"2014-11-13T16:59:23+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/LOGINPROPERTY_in_action.jpg","type":"","width":"","height":""}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2329\/loginproperty\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2329\/loginproperty\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"LOGINPROPERTY \u2013 Getting to know your SQL Logins even more","datePublished":"2014-04-07T15:00:09+00:00","dateModified":"2014-11-13T16:59:23+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2329\/loginproperty\/"},"wordCount":488,"commentCount":1,"image":{"@id":"https:\/\/sqlity.net\/en\/2329\/loginproperty\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/LOGINPROPERTY_in_action.jpg","keywords":["LOGINPROPERTY","security","security management","server principals","SQL Login","SQL Server"],"articleSection":["DMVs & CVs","General","Security","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2329\/loginproperty\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2329\/loginproperty\/","url":"https:\/\/sqlity.net\/en\/2329\/loginproperty\/","name":"LOGINPROPERTY \u2013 Getting to know your SQL Logins even more - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2329\/loginproperty\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2329\/loginproperty\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/LOGINPROPERTY_in_action.jpg","datePublished":"2014-04-07T15:00:09+00:00","dateModified":"2014-11-13T16:59:23+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Find out how LOGINPROPERTY can be used to get additional properties for SQL Logins in SQL Server that not even the sys.sql-logins catalog view reveals.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2329\/loginproperty\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2329\/loginproperty\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2329\/loginproperty\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/LOGINPROPERTY_in_action.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/LOGINPROPERTY_in_action.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2329\/loginproperty\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"LOGINPROPERTY \u2013 Getting to know your SQL Logins even more"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-Bz","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2329","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=2329"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2329\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2329"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2329"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2329"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}