Post reply

SQL Account cannot see the user database

  • February 3, 2020 at 2:08 pm

    #3722604

    Hello,

    I'm not hundred percent sure and I'm supporting and running into SQL LOGIN issues.

    Someone create a SQL Login and grant DBO permission to a user database "AppDB1".

    When he opened the SSMS and connected the AppDB1 with SQL login.

    The AppDB1 did not show up on SSMS.

    Would someone give me a light and helps.

    Thank you in advance.

    -Edwin

  • February 3, 2020 at 2:45 pm

    #3722616

    100% sure that the user has connected to the right server hosting the right database?

    In SSMS you should be able to see all databases on the server, even if you have no access to them.

    So if you cannot see "AppDB1" the user has most likely not connected to the right server

  • February 3, 2020 at 3:08 pm

    #3722632

    Ed7 wrote:

    Someone create a SQL Login and grant DBO permission to a user database "AppDB1".

    I apologize for asking such an obvious question but have you actually verified that happened correctly?  For example, I've seen a fair number of people think that the assignment of "dbo" in the "Default Schema" would grant the privs and didn't actually assign any privs.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 3, 2020 at 4:57 pm

    #3722671

    I verified that the user connect to right server and I was able to duplicate the same issues.

    My Steps to duplicate:

    1. Create SQL login = 'DBATest1' and assigned a password.

    2. Granted Read/Write to sql login 'DBATest1' on AppDB1 on SQLServer1

    3. Open SSMS--> Enter Server Name = SQLServer1, Authentication = SQL Server Authentication, Login id = DBATest1 and password = (provided the password)

    4. And, the AppDB1 did not show up on SSMS on SQL Server 2008 R2 SP3.

    In the past, I crossed this issue and I did not recall it how to fix it.

    Then, I perform the same steps on different SQL Server 2016 and I did not see the same issues.

  • February 3, 2020 at 5:27 pm

    #3722683

    you might need to "GRANT CONNECT"

    MVDBA

  • February 3, 2020 at 5:39 pm

    #3722687

    Ed7 wrote:

    I verified that the user connect to right server and I was able to duplicate the same issues.

    My Steps to duplicate:

    1. Create SQL login = 'DBATest1' and assigned a password.

    2. Granted Read/Write to sql login 'DBATest1' on AppDB1 on SQLServer1

    3. Open SSMS--> Enter Server Name = SQLServer1, Authentication = SQL Server Authentication, Login id = DBATest1 and password = (provided the password)

    4. And, the AppDB1 did not show up on SSMS on SQL Server 2008 R2 SP3.

    In the past, I crossed this issue and I did not recall it how to fix it.

    Then, I perform the same steps on different SQL Server 2016 and I did not see the same issues.

     

    By default, public has VIEW ANY DATABASE permissions. It sounds like this may have been revoked - you may want to check for that. You can compare those permissions between the server you are having the problem with the one where you don't have the problem. Another check for the above steps you took is to write some query or even just one line of USE AppDB1 and just execute that as the user. If that succeeds, the user has access but just can't view the database in SSMS. Which is often due to changing the default public permissions I mentioned.

    Sue

  • February 3, 2020 at 8:21 pm

    #3722737

    I researched and found the SQL Error logs:

    Error: 18401, Severity: 14, State: 1.

    Login failed for user xxxxUser. Reason: Server is in script upgrade mode. Only administrator can connect at this time.

  • February 3, 2020 at 8:28 pm

    #3722738

    I have inheritance SQL Server environment where the Server team, etc wanted to be System Admin on the SQL Server.

    They might perform some upgrade.

    And, I found the work-around.

    https://support.microsoft.com/en-us/help/2163980/fix-the-sql-server-service-cannot-start-after-you-install-cumulative-u

  • February 4, 2020 at 2:30 am

    #3722804

    Ed7 wrote:

    I have inheritance SQL Server environment where the Server team, etc wanted to be System Admin on the SQL Server.

    They might perform some upgrade.

    And, I found the work-around.

    https://support.microsoft.com/en-us/help/2163980/fix-the-sql-server-service-cannot-start-after-you-install-cumulative-u

    That link is about a problem in SQL Server 2008 R2.  Are you saying that you had the same problem in SQL Server 2016?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 4, 2020 at 10:36 am

    This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply