Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Login can only connect to one database Expand / Collapse
Author
Message
Posted Tuesday, October 23, 2012 7:01 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:02 AM
Points: 83, Visits: 509
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
Post #1376015
Posted Tuesday, October 23, 2012 7:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:21 AM
Points: 2,014, Visits: 3,452
Is there a little red down arrow on the user icon in the db they can't connect to?
Post #1376019
Posted Tuesday, October 23, 2012 7:17 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:02 AM
Points: 83, Visits: 509
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
Post #1376023
Posted Tuesday, October 23, 2012 8:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:21 AM
Points: 2,014, Visits: 3,452
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
Post #1376071
Posted Tuesday, October 23, 2012 8:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:02 AM
Points: 83, Visits: 509

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


class class_desc major_id minor_id grantee_principal_id grantor_principal_id type permission_name state state_desc
0 DATABASE 0 0 1 1 CO CONNECT G GRANT
0 DATABASE 0 0 5 1 CO CONNECT G GRANT
0 DATABASE 0 0 6 1 CO CONNECT G GRANT
0 DATABASE 0 0 7 1 CO CONNECT G GRANT
1 OBJECT_OR_COLUMN -505 0 0 1 SL SELECT G GRANT
1 OBJECT_OR_COLUMN -504 0 0 1 SL SELECT G GRANT
1 OBJECT_OR_COLUMN -503 0 0 1 SL SELECT G GRANT
1 OBJECT_OR_COLUMN -502 0 0 1 SL SELECT G GRANT
Post #1376094
Posted Tuesday, October 23, 2012 8:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:21 AM
Points: 2,014, Visits: 3,452
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
Post #1376111
Posted Tuesday, October 23, 2012 9:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:02 AM
Points: 83, Visits: 509
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
Post #1376146
Posted Tuesday, October 23, 2012 9:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:21 AM
Points: 2,014, Visits: 3,452
Yeah, think you're right with the next step being to log in with SSMS - not sure what else to suggest at this point!
Post #1376155
Posted Tuesday, October 23, 2012 11:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:02 AM
Points: 83, Visits: 509
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

Post #1376187
Posted Wednesday, October 24, 2012 2:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:21 AM
Points: 2,014, Visits: 3,452
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.
Post #1376363
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse