SQL Server read-only user can connect local but not remotely

Question

I have a SQL Server where admin users can connect locally and remotely, but read only users can only connect locally and get a "Login failed" error when connecting remotely. It's using SQL Server Authentication. Any ideas on what could be happening?

Further update, the connection only works when i give a user CONTROL DATABASE securables but does not work otherwise. — Jorge Guzman, Apr 29 '11 at 14:33

Peter Schofield · Accepted Answer · 2016-01-08T15:38:48.273

1

Dynamo's advice is good - if you look at the SQL Server logs for SQL Server 2005 or later, and failed logins are audited, it will give you a state code. If you look up the code, it will give you a better idea of why.

MSDN Login Failed: http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

http://www.thirdnorm.com/databases/sql-server/error-18456-understanding-login-failed-error-messages-in-sql-server/

edited Jan 08 '16 at 15:38

answered Apr 28 '11 at 21:33

Peter Schofield

1,639
9
11

Thanks it's state 12 "Valid login but server access failure" which makes it a bit more puzzling :) Any idea? – Jorge Guzman Apr 29 '11 at 12:47
The same user then works again when I assign sysadmin – Jorge Guzman Apr 29 '11 at 13:13

score 1 · Answer 2 · answered Jun 04 '19 at 19:03

1

You can connect remotely if you have read-only access,there may be some error in DB authentication method.Use DB name to connect using right authorization.

answered Jun 04 '19 at 19:03

codebee

11
1

Shane · Answer 3 · 2011-04-28T21:35:11.013

0

It's possible that their default database is not set to a database that they have read access to so they are being denied access as soon as they connect. Make sure that the users have the correct database set to their default and retry the connection.

edited Apr 28 '11 at 21:35

answered Apr 28 '11 at 21:29

Shane

1,869
4
20
34

SQL Server read-only user can connect local but not remotely

3 Answers3