asked
Login Error for Windows Login
Hi. 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: CREATE LOGIN [MyDomain\MyUser] FROM WINDOWS WITH DEFAULT_DATABASE=[msdb], DEFAULT_LANGUAGE=[us_english] I created the user in msdb: CREATE USER [MyDomain\MyUser] FOR LOGIN [MyDomain\MyUser] WITH DEFAULT_SCHEMA=[dbo] 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: - Error Number: 18456 Severity: 14 State: 11 Which means: Valid login but server access failure: [MSDN Blog][1] I have checked the following: 1. Server Properties - Permissions - Connect SQL = True 2. Database Properties - Permissions - Connect = True Additional Info: 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]:
http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx
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.
Hi Pavel, 1) Yes 2) Yes 3) Yes, as far as I can tell. I have never touched Endpoints before. Where/how could I check that? The problem occurs for more than one user and machine. I have also tried adding the AD-Group Domain Users and granting it the permissions, to no avail.
In SSMS: Endpoints you will find under \Server Objects\Endpoints\System Endpoints Permissions on them you can find in the Logins and Server roles permission. Select properties of eg PUBLIC role, under permissions you should see the permissions for the Endpoints Are you encountering the problem only for some users or all users? From what OS are you encountering the problems?
**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.
Hi Anuj, I activated guest in msdb, but this did not help. If I try setting the default DB to master (guest is always active here) it keeps throwing the same error. The strange thing is, that the login has been explicitly added as a user to the database and should therefore be allowed to connect, even if there are no further permissions (view definitions etc).
How do I fix Microsoft login problems?
What can I do to fix my Microsoft account?
Log in with your Microsoft account.
Use your regular password instead of a PIN.
Check your account settings.
Confirm your Microsoft Account security information.
Check your email accounts.
Convert your Microsoft Account into a local account.
Change your privacy settings.
Hope this helps
1 Person is following this question.
