2

For audit purposes, we need to list all SQL logins with their creation and expiration dates. Is there a way to script this for all of my SQL Servers, rather than performing the task manually through the UI?

2

To get the information for a single instance you would use DaysUntilExpiration value for the LOGINPROPERTY function. This will give you the days until the password expires. You can then cast that as an integer and use the DATEADD function to get the actual date.

SELECT name AS LoginName, 
   DATEADD(DAY, CAST(LOGINPROPERTY(name, 'DaysUntilExpiration') AS int), GETDATE()) AS ExpirationDate,
   create_date
   FROM sys.server_principals
   WHERE type = 'S'

Now you can use a few different methods to get this from multiple servers:

  • Multiple instance connection via SSMS and Registered Servers
  • PowerShell using the Invoke-SqlCmd cmdlet within the SQLPS snapin. AS long as you are on a machine with SQL Server 2008 (or higher) management tools, this is available.

Examle:

$q = @"
SELECT name AS LoginName, 
DATEADD(DAY, CAST(LOGINPROPERTY(name, 'DaysUntilExpiration') AS int), GETDATE()) AS ExpirationDate,
create_date
FROM sys.server_principals
WHERE type = 'S'
"@

Add-PsSnapin *SQL*

$serverList = 'Server1','Server2','Server3\InstanceName'

foreach ($s in $serverList)
{
 Invoke-SqlCmd -ServerInstance $s -Database master -query $q
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.