none
Login failed for user 'username'. Reason: Failed to open the explicitly specified database

    Question

  • Perhaps this should go to a different section of the forum; but, I have an error on SQL2008 with a SCOM login.

    Login failed for user 'domain\username'. Reason: Failed to open the explicitly specified database. [CLIENT: local ip addr]
    Error: 18456, Severity: 14, State: 38.

    The domain username is for SCOM.

    I ran a SQL Server Profiler and the following error message appears on Master database

    "The user does not have permission to perform this action."

    Before I grant access to Master database, just wondering if anyone has this problem with SCOM and which db level privileges does it need on Master?

    Thanks.

    Edit: This sql server is not for SCOM. The SCOM ReportServer, etc are on a different instance and different server.

    • Edited by Ami2013 Wednesday, April 23, 2014 3:04 PM
    Wednesday, April 23, 2014 3:02 PM

Answers

  • That was just my bad ... the drop down does just say master

    But that's the default database. The error message talks about an explicitly specified database, so there is something in the connection string that SCOM uses. You need to check that.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, April 24, 2014 9:43 PM
  • I think you need to check for more errors in both the Windows event log and the SQL Server error log. You are (were?) getting an error that you could not access master. by creating a user in master, you should have solved that problem. So you must be getting another error now.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Thursday, April 24, 2014 3:51 PM

All replies

  • Hello,

    Can you go to the database, delete the login, and add it again?


    Is the database online?


    Does the default database for that login exist? Please verify.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com


    Wednesday, April 23, 2014 3:37 PM
    Moderator
  • Default database for that login is Master and it's online.  

    The username does not currently have access to Master.

    Even if I remove the username from logins; I'm getting that error.


    • Edited by Ami2013 Wednesday, April 23, 2014 3:53 PM
    Wednesday, April 23, 2014 3:53 PM
  • To access a database a login must be mapped to a user in that database. (Normally the login and user have the same names which makes it a little hard to tell them apart.) It sounds like, in this case, the login is attempted to use the master database, but the login does not have a corresponding user account in the master database. To make this work, you must create a user account in that database. The easy way is by using Management Studio, right-click the login and click properties. On the User Mapping page, select the master database, so that the login has access to the master database. You can use the CREATE USER statement instead, if you want to perform this action using Transact-SQL.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Wednesday, April 23, 2014 4:07 PM
  • USE [master]
    GO
    CREATE USER [domain\username] FOR LOGIN [domain\username]
    GO

    Created without any database role membership. So, pretty much just public is checked. Still getting the error in the logs.

    Wednesday, April 23, 2014 5:50 PM
  • If the default database is set to Master, change it to master, because that is actually how it is spelled, and on a server with a case-sensitive collation it matters.

    You should not have add users to the master database, since guest user is enabled in this database and cannot be disabled. (And if you created a user, drop it.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, April 23, 2014 9:24 PM
  • Hi,

    As the description, the SCOM user has only public role and has no mapping to the user database and system database. It seems the permission issue. The login has insufficient privilege. Double check with the SQL Profiler and see if the account try to access other database as well. If you assign the sysadmin role to this account and the error should be gone.

    Here is a similar thread:

    http://social.technet.microsoft.com/Forums/en-US/8453abf1-2f9a-48be-805f-4839396b028a/login-failed-for-user-domainusername-reason-failed-to-open-the-explicitly-specified-database

    Thanks.


    Tracy Cai
    TechNet Community Support

    Thursday, April 24, 2014 6:50 AM
    Moderator
  • That was just my bad ... the drop down does just say master

    Thursday, April 24, 2014 1:42 PM
  • Yes, it does try to access other DBs on there as well. And the error does goes away with sysadmin privileges.

    Having said that, my concern is with the sysadmin privileges.

    1) this is not the SCOM sql server / instance. This is one of the sql servers that SCOM monitors.

    2) Other SQL2008 with the same build (dev, test and prod environments) does not even have this particular domain\username. So, why does this particular instance requires a username with sysadmin privileges?



    • Edited by Ami2013 Thursday, April 24, 2014 1:48 PM
    Thursday, April 24, 2014 1:47 PM
  • I think you need to check for more errors in both the Windows event log and the SQL Server error log. You are (were?) getting an error that you could not access master. by creating a user in master, you should have solved that problem. So you must be getting another error now.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Thursday, April 24, 2014 3:51 PM
  • That was just my bad ... the drop down does just say master

    But that's the default database. The error message talks about an explicitly specified database, so there is something in the connection string that SCOM uses. You need to check that.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, April 24, 2014 9:43 PM