SQL 2012 - change server to mixed authentication now cannot see databases

Looks like the login you used to access the SQL Mgmt studio doesnt have access to view user databases. Are you trying to use sysadmin account - domain account or sa or any specific account?

Also, if u r using any specific login, do specify what level of access it is having at server and db level.

Im using my domain admin account. As before the reboot this could see all databases.

Do you see the SQL Server running in SQL Configuration manager?

Changing to mixed should not cause this to happen as Windows auth still is there. Did you give the sa user a password when you changed to mixed?

Can you use sa to login to the SQL Server?

There is a way to get back in, so no worries there, just want to see what you can see before we use the big hammer.

SQL is running but I can only see the basic system databases.

the sa account is currently granted permission to connect to databases but not to login.

I can see my databases files still in the folder directories.

Is your Windows User in the sysadmin role on the SQL Server?

yes it is

When you login, what results do you get from this query?

SELECT l.name as grantee_name, p.state_desc, p.permission_name
FROM sys.server_permissions AS p JOIN sys.server_principals AS l
ON   p.grantee_principal_id = l.principal_id
WHERE permission_name = 'VIEW ANY DATABASE' ;

Public - Grant - VIEW ANY DATABASE


Not sure whether I have mentioned that the databases that are missing are all currently offline.

Well, they should still have the database listed and a status of Offline.  But you don't see any databases listed under Databases in SSMS?

No Im afraid not. I only see 2 databases plus the default DB's that are stored in the location:

D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA

However the databases that I have lost since rebooting are in the location:

D:\Program Files\Microsoft SQL Server\MSSQL11.SCCM\MSSQL\DATA


I am confused by this as im sure there wasnt another instance.

Well, the path that includes MSSQL11.SCCM indicates that there is a named instance called SCCM.

So you will want to connect to that instance to see the other databases.

localhost\SCCM if you are on that server trying to connect.

OK I have now looked in the services and I can see there is another instance of SQL which has failed to start. So apologies i didnt realise this. When looking in the event log there seems to be a port conflict with the 2 instances. Soon as I stopped the other instance ans started the other it worked. I can now see my other databases.

Why would this conflict?

Also would changing the SQL authentication to mixed cause problems?