I am having difficulties getting a user to connect to a database on a sandbox system.
I have the windows user as a server login, defaulting to msdb:
I created the user in msdb:
The user is a member of the public database role in the msdb, with permissions to see stuff like sys.tables.
When I try to connect using SSMS, I get an error in the SQL Server Error Log:
Which means: Valid login but server access failure: MSDN Blog
I have checked the following:
The users sys, INFORMATION_SCHEMA and guest are disabled. The error occurs for all databases on the server, it is not just for msdb.
If I bump the user to a sysadmin I get straight in, so it must have something to do with the permissions as they are. Where do I look and what have I missed?
1) Check whether the user(login) has Connect SQL permission on the server
2) Check whether the user (login) is member of the PUBLIC server role, which has by default connect permission on the default End Points in the server.
3) If the login is member of the PUBLIC role, check, that the PUBLIC role has Connect permission on the End Point you are using and connecting to.
Also this problem occurs when connection from one machine or from more machines? I saw somewhere, that disabling simple sharing mode helped some people, then when this was enabled, user was authenticated as guest on the server.
answered Jul 19 '10 at 06:30 AM
Enable the guest account
I think the problem is with your guest users,the guest username is a member of the public role. After the guest user has been added to a database, any individual with a valid SQL Server login regardless of security mode can access the database as the guest user. A guest user works as follows:
SQL Server checks to see whether the login ID has a valid username or alias assigned. If so, SQL Server grants the user access to the database as the username or aliases.
If not, SQL Server checks to see whether a guest username exists. If so, the login ID is granted access to the database as guest. If the guest account does not exist, SQL Server denies access to the database.
answered Jul 19 '10 at 04:05 AM