Sql-server – SSMS 2016 forced connection

errorsloginssql serversql-server-2016ssms

Some developers use SSMS 2016 with db_owner permissions to database(s) on a shared SQL Server, but error logs show an attempted login to the first user database on the instance, which they don't have access to. The log error is the typical 18456, but the user didn't try to connect to that database (I watched). Also, SSMS clocked for about 5-10 seconds when trying to expand the databases folder. After some time all the databases on the server were present. This hasn't been an issue using SSMS 2014. As a work around we granted 'connect to all databases', for the user which did stop the errors in the log and removed the delay when expanding the database folders, but doesn't seem like the long term solution.

Does this sound like a SSMS 2016 bug or user permissions? Any thoughts?

Thanks in advance,

TJ

Default db is set to the user database with db_owner permissions. This has been tested on different servers with the same login error result, and login was to the first user database. This is a AD user.
Error: 18456, Severity: 14, State: 38

Best Answer

@sqlband I can confirm that this a bug ( or feature) of SSMS 2016.

I was able to reproduce this in SSMS version Microsoft SQL Server Management Studio 13.0.16106.4.

USE [master]
GO

CREATE LOGIN [ssmsspeed] WITH PASSWORD=N'ssmsspeed', DEFAULT_DATABASE=[WideWorldImporters], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [WideWorldImporters]
GO
CREATE USER [ssmsspeed] FOR LOGIN [ssmsspeed]
GO
USE [WideWorldImporters]
GO
ALTER ROLE [db_owner] ADD MEMBER [ssmsspeed]
GO

Now connecting to SSMS using ssmsspeed login. When I expand the database node it takes about 10 seconds to expand as explained by OP. I can see this in my error log and in my security audit file.

Message Login failed for user 'ssmsspeed'. Reason: Failed to open the explicitly specified database 'AdventureWorks2014'. [CLIENT: ]

Error: 18456, Severity: 14, State: 38.

If I list my user databases order by name AdventureWorks2014 is the first one.

  • AdventureWorks2014
  • GMODBA
  • hacked
  • test
  • Warehouse
  • WideWorldImporters

I also tested this by making Master database as Default_database but same result.

USE [master]
GO

CREATE LOGIN [ssmsspeed1] WITH PASSWORD=N'ssmsspeed1', DEFAULT_DATABASE=[Master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [WideWorldImporters]
GO
CREATE USER [ssmsspeed1] FOR LOGIN [ssmsspeed1]
GO
USE [WideWorldImporters]
GO
ALTER ROLE [db_owner] ADD MEMBER [ssmsspeed1]
GO

When I do the same test in Microsoft SQL Server Management Studio 12.0.4100.1 I cannot reproduce this. Expanding database node is instantaneous and no login fail message in error log.

Related Question