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?