USE MASTER
GO

BEGIN
	DECLARE @SQLVerNo INT;

	SET @SQLVerNo = cast(substring(CAST(Serverproperty('ProductVersion') AS VARCHAR(50)), 0, charindex('.', CAST(Serverproperty('ProductVersion') AS VARCHAR(50)), 0)) AS INT);

	CREATE TABLE #TUser (
		ServerName VARCHAR(256)
		,DBName SYSNAME
		,[Name] SYSNAME
		,GroupName SYSNAME NULL
		,LoginName SYSNAME NULL
		,default_database_name SYSNAME NULL
		,default_schema_name VARCHAR(256) NULL
		,Principal_id INT
		,[sid] VARBINARY(85)
		)

	IF @SQLVerNo = 8
	BEGIN
		INSERT INTO #TUser
		EXEC sp_MSForEachdb '
	 SELECT 
	   @@SERVERNAME,
	   ''?'' as DBName,
	   u.name As UserName,
	   CASE WHEN (r.uid IS NULL) THEN ''public'' ELSE r.name END AS GroupName,
	   l.name AS LoginName,
	   NULL AS Default_db_Name,
	   NULL as default_Schema_name,
	   u.uid,
	   u.sid
	 FROM [?].dbo.sysUsers u
	   LEFT JOIN ([?].dbo.sysMembers m 
	   JOIN [?].dbo.sysUsers r
	   ON m.groupuid = r.uid)
	   ON m.memberuid = u.uid
	   LEFT JOIN dbo.sysLogins l
	   ON u.sid = l.sid
	 WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1
	   /*and u.name like ''tester''*/
	 ORDER BY u.name
	'
	END
	ELSE IF @SQLVerNo >= 9
	BEGIN
		INSERT INTO #TUser
		EXEC sp_MSForEachdb '
	 SELECT 
	   @@SERVERNAME,
	   ''?'',
	   u.name,
	   CASE WHEN (r.principal_id IS NULL) THEN ''public'' ELSE r.name END GroupName,
	   l.name LoginName,
	   l.default_database_name,
	   u.default_schema_name,
	   u.principal_id,
	   u.sid
	 FROM [?].sys.database_principals u
	   LEFT JOIN ([?].sys.database_role_members m
	   JOIN [?].sys.database_principals r 
	   ON m.role_principal_id = r.principal_id)
	   ON m.member_principal_id = u.principal_id
	   LEFT JOIN [?].sys.server_principals l
	   ON u.sid = l.sid
	 WHERE u.TYPE <> ''R''
	   /*and u.name like ''tester''*/
	 order by u.name
	 '
	END

/*
 SET @username = 'XX' - Input SQL login name to report on. Replace XX with login.
 BadPasswordCount- Returns the number of consecutive attempts to log in with an incorrect password.
 BadPasswordTime- Returns the time of the last attempt to log in with an incorrect password.
 DaysUntilExpiration- Returns the number of days until the password expires.
 DefaultDatabase- Returns the SQL Server login default database as stored in 
metadata or master if no database is specified. 
Returns NULL for non-SQL Server provisioned users; 
for example, Windows authenticated users.
 DefaultLanguage- Returns the login default language as stored in metadata.Returns NULL for non-SQL Server provisioned users, for example, Windows authenticated users.
 HistoryLength- Returns the length of time the login has been tracked using the password-policy enforcement mechanism.
 IsExpired- Returns information that will indicate whether the login has expired.
 IsLocked- Returns information that will indicate whether the login is locked. 
 IsMustChange- Returns information that will indicate whether the login must change its password the next time it connects. 
 LockoutTime- Returns the date when the SQL Server login was locked out because it had exceeded the permitted number of failed login attempts.
 PasswordHash- Returns the hash of the password.
 PasswordLastSetTime- Returns the date when the current password was set.
*/
	SELECT a.ServerName
		,CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
			WHEN 1
				THEN 'WINDOWS AUTHENTICATION ONLY'
			WHEN 0
				THEN 'SQL & WINDOWS AUTHENTICATION'
			END AS 'SECURITY MODE'
		,a.DBName
		,a.NAME AS RoleName
		,a.GroupName
		,a.LoginName
		,a.default_database_name
		,a.default_schema_name
		,a.Principal_id
		,a.[sid]
		,convert(INT, loginproperty(a.LoginName, 'BadPasswordCount')) [Bad Password Count]
		,convert(DATETIME2(0), loginproperty(a.LoginName, 'BadPasswordTime')) [Bad Password Time]
		,cast(loginproperty(a.LoginName, 'DaysUntilExpiration') AS INT) [Days Until Expiration]
		,convert(BIT, loginproperty(a.LoginName, 'IsExpired')) [Is Expired]
		,convert(BIT, loginproperty(a.LoginName, 'IsLocked')) [Is Locked]
		,convert(BIT, loginproperty(a.LoginName, 'IsMustChange')) [Is Must Change]
		,convert(DATETIME2(0), loginproperty(a.LoginName, 'LockoutTime')) [Lockout Time]
		,convert(DATETIME2(0), loginproperty(a.LoginName, 'PasswordLastSetTime')) [Password Last Set Time]
	FROM #TUser a
	ORDER BY DBName
		,[name]
		,GroupName

	DROP TABLE #TUser
END