Problem: Property IsLocked is not available for Login ‘[sa]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)
Solution: You will get the above error, in case of one of the below issue.
- SQL Server authentication mode is Windows Only
- SA Account is disable
- SA account is locked
SQL Server authentication mode is Windows Only
You can check the SQL Server authentication mode using below query.
SELECT
CASE
SERVERPROPERTY(‘IsIntegratedSecurityOnly’)
WHEN 1 THEN
‘Windows Authentication’
WHEN 0 THEN
‘Windows and SQL Server Authentication’
END
as [Authentication Mode]
If the authentication mode Windows, you have to change the authentication mode is Mix Mode. You can change the authentication mode by right click on Server Properties -> Security tab.
This requires SQL Server Service restart.
SA Account is locked/Disable
You can execute below query to check whether SA account is locked or not. If the account is locked it will return 1 and 0 for un-locked.
SELECT
LOGINPROPERTY(‘sa’,
‘IsLocked’)
You can investigate whether SA account is locked by bad Password using below query. It will return the count of consecutive failed login attempts
SELECT
LOGINPROPERTY(‘sa’,
‘BadPasswordCount’);
GO
Right click on SA account and check the SA account properties, check the below properties of the SA login whether it is disabled or locked.
Execute the below script to unlock and enable the SA account
ALTER
LOGIN [sa] WITH
PASSWORD=N’Password’,
DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
ALTER
LOGIN [sa] ENABLE
GO
