5

I have executed the following:

USE [MyDatabase];
GO
CREATE LOGIN [MyDomain\MyAccount] FROM WINDOWS WITH DEFAULT_DATABASE=[MyDatabase];
CREATE USER [MyDomain\MyAccount] FOR LOGIN [MyDomain\MyAccount];
GRANT EXECUTE   ON SCHEMA::[MySchema] TO [MyDomain\MyAccount];
GRANT SELECT    ON SCHEMA::[MySchema] TO [MyDomain\MyAccount];
GRANT INSERT    ON SCHEMA::[MySchema] TO [MyDomain\MyAccount];

(I am not granting any roles as I do not want this login to be able to access any other schema in this database.)

I run SSMS as MyDomain\MyAccount (run as a different user). I then try to connect to this instance. However, I am getting:

Cannot open user default database. Login failed. Login failed for user
'MyDomain\MyAccount'. (Microsoft SQL Server, Error: 4064)

I get the same result if I set the default database to MyDatabase in the Connection Properties.

I ran a Profiler trace and see an error 18456 state 38:

Login failed for user 'MyDomain\MyAccount'. Reason: Failed to open the explicitly specified database 'MyDatabase'. [CLIENT: MyIpAddress]

That is followed by an error 18456 state 40:

Login failed for user 'MyDomain\MyAccount'. Reason: Failed to open the database 'MyDatabase' specified in the login properties. [CLIENT: MyIpAddress]

MyDatabase is online and I have no trouble connecting to it with a different login. What additional permission do I need to grant to MyDomain\MyAccount? Is there something else that I am missing?

  • Have you verified the login is enabled? – Steve Mangiameli Jul 13 '15 at 18:50
  • @Steve: The login is enabled. – Mark Freeman Jul 13 '15 at 19:25
3

Off guess you are missing the connect permission. I'd always thought that was created with the user but there may be conditions where it isn't added.

Try running

USE [MyDatabase];
GO
GRANT CONNECT TO [MyDomain\MyAccount];

You could also try changing the default schema of [MyDomain\MyAccount]

ALTER USER [MyDomain\MyAccount] WITH DEFAULT_SCHEMA = MySchema;

Lastly you can use my sp_dbpermissions and sp_srvpermissions to review the permissions for the user.

EXEC sp_srvpermissions 'MyDomain\MyAccount'; -- Return instance level permissions
EXEC sp_dbpermissions 'All','MyDomain\MyAccount'; -- Return DB level permissions for all databases
  • 2
    GRANT CONNECT TO [MyDomain\MyAccount]; solved it. Apparently that was not being automatically granted in this case. Thanks! – Mark Freeman Jul 13 '15 at 19:25
  • 1
    LOL...that was my next question. Amazingly, depending on the version and environnment - especially when scripting logins - the connect permission is almost always missing and the login is disabled. Nice work Mark – Steve Mangiameli Jul 14 '15 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.