Dear Friends,

I want to share a good news with you all, I am again 3rd time awarded as MVP in SQL Server. Thanks for reading my site, I will keep posting the best article and always there to solve your queries.

Thanks,
Jugal Shah

You will see the 2012 in below award shortly -:)

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

Step 1: Launch SQL Server Management Studio and In the Object Explorer expand the Management folder.

Step 2: Right click on the Maintenance Plans folder and select New Maintenance Plan.

Step 3: Give the meaning full name to maintenance plan.

Step 4: From the Toolbox drag and drop a Check Database Integrity Task, Rebuild Index Task, Update Statistics Task and place them vertically in the same order.

Step 5: Connect the tasks together by dragging the arrow from one box to the other so they are connected as: Check Database Integrity Task – Rebuild Index Task – Update Statistics Task.

Step 6 Right click on the each tasks and select the databases in maintenance plan.

Step 7 Schedule a maintenance plan to run in off business hours.

Step 1: Launch SQL Server Management Studio.

Step 2: In the object explorer, right click on the database and select Reports -> Standard Reports -> Index Physical Statistics.

Step 3: SQL Server Management Studio will generate a report showing information about the Table Names, Index Names, Index Type, Number of Partitions and Operation Recommendations.

Step 4: Repeat the above steps to check the fragmentation of all user databases.

One key value that is provided in the report is the Operation Recommended field. Any value of Rebuild is an indication that the index is fragmented.

By expanding the # Partitions field, you can see the % of fragmentation for a given index.

Report looks like below.