SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Login can only connect to one database


Login can only connect to one database

Author
Message
NJDave
NJDave
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 598
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
Gazareth
Gazareth
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3995 Visits: 5807
Is there a little red down arrow on the user icon in the db they can't connect to?
NJDave
NJDave
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 598
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
Gazareth
Gazareth
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3995 Visits: 5807
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
NJDave
NJDave
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 598
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
Gazareth
Gazareth
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3995 Visits: 5807
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
NJDave
NJDave
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 598
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
Gazareth
Gazareth
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3995 Visits: 5807
Yeah, think you're right with the next step being to log in with SSMS - not sure what else to suggest at this point!
NJDave
NJDave
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 598
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
Gazareth
Gazareth
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3995 Visits: 5807
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search