Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Tuesday, September 13, 2011

I don't know why, but this simple request for SOX Compliance took me a while to find. There is a function that retrieves login properties, LOGINPROPERTY, duh! Of interest is the PasswordLastSetTime.

It also has many other useful properties, see: http://msdn.microsoft.com/en-us/library/ms345412(v=SQL.100).aspx . It now becomes very easy to drop into any query, below is one of many sox queries we run that is now required to include this:


SELECT --l.sid, loginname AS [Login Name], loginproperty(loginname,'PasswordLastSetTime') as PasswordLastSetTime, dbname AS [Default Database], CASE isntname WHEN 1 THEN 'AD Login' ELSE 'SQL Login' END AS [Login_Type], sl.is_disabled, sl.is_policy_checked, sl.is_expiration_checked, CASE WHEN isntgroup = 1 THEN 'AD Group' WHEN isntuser = 1 THEN 'AD User' ELSE '' END AS [AD Login Type], CASE sysadmin WHEN 1 THEN 'Yes' ELSE 'No' END AS [sysadmin], CASE [securityadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [securityadmin], CASE [serveradmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [serveradmin], CASE [setupadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [setupadmin], CASE [processadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [processadmin], CASE [diskadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [diskadmin], CASE [dbcreator] WHEN 1 THEN 'Yes' ELSE 'No' END AS [dbcreator], CASE [bulkadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [bulkadmin] FROM master.dbo.syslogins l left outer join sys.sql_logins sl on l.sid = sl.sid ORDER BY[Login Name], [Login_Type], [AD Login Type]
Tuesday, September 13, 2011 12:40:27 PM (Central Standard Time, UTC-06:00) |  | SQL Server#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
Default Trace - Heavy load, turn it...
SQL 2008 R2 License / Cost = Open S...
Door Installation
Recent Posts
Archive
October, 2019 (1)
September, 2019 (1)
August, 2019 (1)
May, 2019 (1)
April, 2019 (1)
March, 2019 (1)
February, 2019 (1)
January, 2019 (1)
December, 2018 (1)
November, 2018 (3)
October, 2018 (1)
June, 2018 (1)
May, 2018 (3)
April, 2018 (2)
February, 2018 (3)
January, 2018 (3)
November, 2017 (1)
October, 2017 (1)
August, 2017 (1)
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll