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?
Best Answer
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
You could also try changing the default schema of [MyDomain\MyAccount]
Lastly you can use my sp_dbpermissions and sp_srvpermissions to review the permissions for the user.