Login can only connect to one database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Login can only connect to one database

12 posts, Page 1 of 2

1 2 »»

Rate Topic

Display Mode

Topic Options

Author

Message

NJDave

Posted Tuesday, October 23, 2012 7:01 AM

Valued Member

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 68, Visits: 377

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

Gazareth

Posted Tuesday, October 23, 2012 7:11 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 4:11 AM
Points: 1,474, Visits: 2,344

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

Post #1376019

NJDave

Posted Tuesday, October 23, 2012 7:17 AM

Valued Member

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 68, Visits: 377

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

Gazareth

Posted Tuesday, October 23, 2012 8:09 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 4:11 AM
Points: 1,474, Visits: 2,344

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

NJDave

Posted Tuesday, October 23, 2012 8:30 AM

Valued Member

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 68, Visits: 377

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

Gazareth

Posted Tuesday, October 23, 2012 8:58 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 4:11 AM
Points: 1,474, Visits: 2,344

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

NJDave

Posted Tuesday, October 23, 2012 9:41 AM

Valued Member

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 68, Visits: 377

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

Gazareth

Posted Tuesday, October 23, 2012 9:48 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 4:11 AM
Points: 1,474, Visits: 2,344

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

NJDave

Posted Tuesday, October 23, 2012 11:44 AM

Valued Member

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 68, Visits: 377

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

Gazareth

Posted Wednesday, October 24, 2012 2:54 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 4:11 AM
Points: 1,474, Visits: 2,344

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 »

12 posts, Page 1 of 2

1 2 »»

Permissions