Bug Fix: 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


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.

  1. SQL Server authentication mode is Windows Only
  2. SA Account is disable
  3. 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

Advertisements

2 thoughts on “Bug Fix: 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

  1. Pingback: Link Resource # 44 : Mar 24 – Mar 31 « Dactylonomy of Web Resource

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s