2

When accessing SQL Server with SSMS versions (2012/2014 and 2016), opening the databases list from the object explorer is really slow. If I try it using SSMS 2008 R2 on the same instance the result is => instant display.

Note : The account is sysadmin, all databases have autoclose disabled, and I am not connecting to a remote server. It's on my local machine!

I noticed that there is a small difference comparing the queries generated from SSMS 2008 and 2012/2014/2016 when opening the databases list.

All of them are using has_dbaccess function, except SSMS 2008.

For instance:

SELECT
dtb.name AS [Name],
dtb.database_id AS [ID],
CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible]
FROM
master.sys.databases AS dtb
ORDER BY
[Name] ASC

There is a huge performance difference if I remove the function. I guess they should add a condition in the begging of the query where in case it's a sysadmin account, just don't check permissions in every single database. Does it make any sense?

3
  • Well, it is ambiguous, "all databases are not configured with autoclose" means to me that they aren't all set to auto-close, but some might be, so I can understand why RLF suggested it... Feb 9, 2017 at 0:15
  • Have you tried this connect.microsoft.com/SQLServer/feedback/details/768124/…
    – jesijesi
    Feb 9, 2017 at 8:41
  • Hi @jesijesi. Yes, I've tried this one as well, even saying the problem is related when connecting to remove servers. Thanks! I really believe the problem is related to that function has_dbaccess. Because when I remove it (it doesn't exist on SSMS 2008) it runs instantaneously. Feb 9, 2017 at 20:18

2 Answers 2

1

This issue is finally fixed in the new SSMS 17.9.

You can get it here.

0

I solved this by changing from Windows Authentication to SQL Server authentication, as proposed here. (I realize not everyone can do that.)

The problem is still there in SSMS 18.9 and SQL Server 2019.

The has_dbaccess function definitely seems to be the problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.