Login can only connect to one database

  • Hello

    Is there any reason why a login could connect to one database and not another?

    The login was created with master as the default database - he can connect to database1 but not database2, database3, etc.

    I created the user in each database in exactly the same way.

    Everything looks the same - I can see the user under "Security" for all databases and all of the databases are checked in "User Mappings"

    Version:

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    Is there something I could have missed?

    Thanks

    Dave

  • Is there a little red down arrow on the user icon in the db they can't connect to?

  • No, no red arrow, its enabled.

    I also tried creating a second login and adding the second login as users to each database and it worked.

    Thanks

    Dave

  • Interesting! Doesn't sound like you have the orphaned user problem either.

    Another thing to check would be the user has connect permission on the database - take a look in sys.database_permissions

  • Just to be sure I didn't have an orphaned login, I ran sp_change_users_login 'report' and got zero results

    I ran select * from sys.database_permissions and got the results below (not all rows, the rest have negative numbers for the major_id, they are system objects) . They are the same on the database that the user can login and the one that he cannot

    How do these results verify if a specific login can log in or that the database user was created correcty? I looked here - http://msdn.microsoft.com/en-us/library/ms188367.aspx - but don't see the connection.

    Thanks

    Dave

    classclass_descmajor_idminor_idgrantee_principal_idgrantor_principal_idtypepermission_namestatestate_desc

    0DATABASE0011CO CONNECTGGRANT

    0DATABASE0051CO CONNECTGGRANT

    0DATABASE0061CO CONNECTGGRANT

    0DATABASE0071CO CONNECTGGRANT

    1OBJECT_OR_COLUMN-505001SL SELECTGGRANT

    1OBJECT_OR_COLUMN-504001SL SELECTGGRANT

    1OBJECT_OR_COLUMN-503001SL SELECTGGRANT

    1OBJECT_OR_COLUMN-502001SL SELECTGGRANT

  • Hi Dave,

    It was the connect permission I was looking for, just wanted to check it was there.

    What's the specific error you get when trying to connect? Anything in the SQL Server Logs?

    Is it an AD or SQL Server Login in question? Is it a member of any server or database roles?

    Thanks

    Gaz

  • Hello

    I don't know the specific error message from the application - I was going back and forth in e-mail with the end user and he only said that the login created only worked on database1, but not database2, database3, etc

    I checked the log and didn't see anything out of the ordinary.

    Login uses SQL server Authentication.

    Database Role membership: db_datareader (on all databases involved)

    Server Role: public

    I haven't heard back from the end-user. I was going to get the user and password and log into SSMS to see if I can then question the application.

    Thanks

    Dave

  • Yeah, think you're right with the next step being to log in with SSMS - not sure what else to suggest at this point!

  • I got the username and password and I can open tables/select records in each database in SSMS.

    User is getting an error...

    Test connection unsuccessful (4) Unable to query mssql server (check Database Name)

    The database name is correct.

    The application is McAfee Enterprise Security Manager. The entries are IP Address, Instance Name, User Id, Password, Port, Database Name

    One database he can connect to without a problem and he is using the same IP Address, Instance Name, User Id, Password, & Port

    Thank you for your help

  • Ok, I'm not familiar with that app so can't offer any insight there.

    The only thing I can think of now is checking the case of the Database Name, if you're using a case-sensitive collation.

    That's the last roll of the dice for me though!

    If the other connection works & settings are the same then it rules out network/firewall issues.

  • Does your SQL Server audit failed login attempts? If it does, what is written to the errorlog at the time of the login failures?

    John

  • My SQL Server does audit failed login attempts. But I dont see anything written to the logs.

    I did get this...

    10/22/2012 15:16:13,Logon,Unknown,Login failed for user 'SIEM_INSTALL'. Reason: The password of the account must be changed. [CLIENT: 10.33.19.10]

    but the user did change the password, gave the password to me, and I was able to open all databases in SSMS

    I also ruled out the case sensitivity possibility that was posted earlier

    Does your SQL Server audit failed login attempts? If it does, what is written to the errorlog at the time of the login failures?

    John

    Still not sure, going to take a shot by creating another user and talk to the person that installed SQL on the box - if network and firewall rule is ruled out and no messages in the log - but the login works correctly on 1 out of 5 databases on the same instance - weird - thanks for ypur help

Viewing 12 posts - 1 through 11 (of 11 total)

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