Category Archives: Notes

All Articles

Master..spt_values system table

Leave a reply

SPT_Values is un-documented table. It seems it is available from Sybase days and it is used in system stored procedures to translate codes into readable strings.
It stores the specific number of each SQL Server Property. If you execute below query, you can get the diffrent kind of result and you can use it as per the need.

select min(number) FROM master..spt_values
–Output
-32768

select min(number) FROM master..spt_values where number > 0
— Output
1
select max(number) FROM master..spt_values
— Output
1469283328

You can use to generate number from 1 to 2048 as well.

SELECT distinct number
FROM master..spt_values
WHERE number BETWEEN 10 AND 15

Unable to change autogrowth properties of SQL Server of log file

Leave a reply

Problem
People often complains that they are unable to change the Log File Growth to un-restricted.

Solution
Whenever we are chaging max size as unlimited for log file, SQL Server reset the Max Size avalue to 2TB and this is the max size supported for log file. This is by design only.

Please check the below image for more detail.

LOGINPROPERTY Built-In function

2 Replies

SQL Server 2005/2008 built-in function LOGINPROPERTY can help us retrieve the important information of login properties.

We can retrieve the below important details using LOGINPROPERTY function.
1. Date of last password change
2. Login is locked or not
3. Loign password expired
4. Need to change password at next login or not
5. Count of consecutive failed login attempts
6. DateTime when the last login failed
7. DateTime when the login was locked out
8. Password hash
9. Returns the number of days until the password expires. LOGINPROPERTY(‘sa’, ‘DaysUntilExpiration’) argument is only available with SQL Server 2008

SELECT
name,
CASE LOGINPROPERTY(name, 'IsLocked')
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'Unknown'
END as IsAccountLocked,
LOGINPROPERTY(name, 'PasswordLastSetTime') as PasswordLastSetDate,
LOGINPROPERTY(name, 'BadPasswordCount') as CountOfFailedLoginAttempts,
LOGINPROPERTY(name, 'BadPasswordTime') as LastFailedLoginTime,
LOGINPROPERTY(name, 'LockoutTime') as LoginLockedOutDateTime,
CASE LOGINPROPERTY(name, 'IsExpired')
WHEN 0 THEN 'Password is not expired'
WHEN 1 THEN 'Password is not expired,change it'
ELSE 'Unknown'
END as PasswordExpired,
CASE LOGINPROPERTY(name, 'IsMustChange')
WHEN 0 THEN 'Must not change password at next login'
WHEN 1 THEN 'Must change password at next login'
ELSE 'Unknown'
END as PasswordChangeOnNextLogin,
LOGINPROPERTY(name, 'DaysUntilExpiration') as 'DaysUntilthePasswordExpires', --New Property in SQL Server 2008
LOGINPROPERTY(name, 'PasswordHash') as PasswordHash
From sys.sql_logins
order by name

Output