2

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

  • 2
    What is the default database set for that user ? that might be an issue. – Kin Shah Sep 20 '17 at 16:03
  • You might want to check and make sure the user has access to the master database as well. – Norman Heyen Sep 20 '17 at 16:16
  • 1
    Doesn't sound like a bug so far, but it could be, seen worse. Have you tried osql or sqlcmd? Tried for a different host or the server directly? Is this a SQL User or an AD user? – Ali Razeghi Sep 20 '17 at 16:26
  • Default is set to the database with db_owner. We have tried this on different servers with the same login error result. This is an AD user. – sqldband Sep 20 '17 at 16:40
  • @sqldband, What you are getting along with Error: 18456, Severity: (?), State: (?). – Md Haidar Ali Khan Sep 20 '17 at 17:15
1

@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.

0

This is SSMS 2016 intellisense bug.

Similar behaviour was reported here: SSMS 16.5 - IntelliSense try to connect to offline databases

To get rid of it just disable Intellisense: Tools -> Options -> Text Editor -> Trasact-SQL -> IntelliSense - uncheck Enable IntelliSense

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.