SQL Server Authentication error: Property IsLocked is not available for Login '[sa]'
Recently I had this problem when trying to connect to the SQL Server on a VPC that is running on Vista Ultimate x64.
Initially the SQL Server 2005 instances on that VPC installed with required levels of Service Packs and recently installed SQL Server 2005 SP3 too, additional information on the error is as follows:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
------------------------------
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)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=IsLocked&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
From the above text as you can see that error occurred when trying to connect using SQL Server Management studio.
I ran into the same issue today when I installed an instance of SQL 2005 using Windows Auth. mode. After the install I switched the server over to SQL and Windows Auth mode and ran into the 'sa' problem. I ran the following command and then could see the properties of 'sa' within mgmt studio.
Another thought came into mind that this error started when I have changed the authentication of that SQL instance from Windows to Mixed for a testing purpose. In order to resolve that followed the steps defined below:
Initially when I have installed SQL 2005 instance I have disabled SA login using:
ALTER LOGIN [sa] DISABLE
So the next step is to enable SA as one of the test code uses SA login explicitly (I know it is not a best practice but for this testing purpose it is required). To achieve run the following code:
ALTER LOGIN [sa] WITH PASSWORD=N'secuireone',CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF |
GO |
ALTER LOGIN [sa] ENABLE |
Job done here that SA login has been ENABLED and sometimes when I tried to view the properties under Security --> Logins getting following error:
Cannot open user default database. Login failed.
Login failed for user 'sa'. (Microsoft SQL Server, Error: 4064)
To avoid this error too you have to change the default database of SA login to MASTER :
ALTER LOGIN [sa] WITH PASSWORD=N'secuireone',CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],
