Cannot open user default database. Login failed.

Question

Cannot open user default database. Login failed.

sqld-_-ba

SSCarpal Tunnel

Points: 4365
More actions
September 12, 2012 at 8:20 am

#267605

SQL1 - Microsoft SQL Server 2008 SP1 - 10.0.2789.0
my NT login - COMPANY\ME
i am part of AD Security Group - COMPANY\DBA
COMPANY\DBA login exists on SQL1 as sysadmin, default database is master. (I checked this by logging in as sa)
But when i try to login via SSMS, it errors 'Cannot open user default database. Login failed. Login failed for user 'COMPANY\ME''
Under Connection Properties Tab, if i change 'Connect to database' from '<default>' to 'master' - works fine.
sqlcmd -S SQL1 -d master - works fine.
I dropped and recreated the login. Added login as user on master. Ran - ALTER LOGIN COMPANY\DBA WITH DEFAULT_DATABASE=MASTER
But again, same problem occurs unless i manually specify the default db as master.
I understand that SSMS will remember my previous settings so I don't have to do it each time.
But WHY DO I NEED TO DO THAT ? For all other servers, it works fine as '<default>'.. what is the root cause of this problem?
Because one of my dba colleague is able to connect fine with '<default>'

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sestell1 SSChampion Points: 10230 More actions · Answer 1

You are a member of Company\DBA which was added as a login, but do you also have a login created for Company\ME with a different default database specified? Are you a member of any other groups that have been added as logins?

You can create a trace on login attempts to capture exactly what database it believes is your default when you try to log in. That might be a clue as to what is happening.

sqld-_-ba SSCarpal Tunnel Points: 4365 More actions · Answer 2

Great!

1. Ran a trace and got a Login Failed. These were the interesting columns :

GroupId - BLANK (was 1 when it succeeded when i manually put default db)

LoginSid - BLANK (was OX01... when it succeeded..)

SessionLoginName - COMPANY\ME (was BLANK when it succeeded)

DatabaseName - master (both during success & failure)

All other columns were pretty much same when it succeeded & failed.

2. Turns out I WAS part of another group COMPANY\APPUSERS, but the colleague was not.

3. COMPANY\APPUSERS had a blank default database under Login Properties. I changed it to master.

4. It works FINE now without specifying default db manually.

This raises couple questions:

1. What does the GroupID column in profiler mean ?

2. If a user is part of 2 different groups, which one's permissions are used?

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 3

waeva (9/12/2012)
Great!
1. Ran a trace and got a Login Failed. These were the interesting columns :
GroupId - BLANK (was 1 when it succeeded when i manually put default db)
LoginSid - BLANK (was OX01... when it succeeded..)
SessionLoginName - COMPANY\ME (was BLANK when it succeeded)
DatabaseName - master (both during success & failure)
All other columns were pretty much same when it succeeded & failed.
2. Turns out I WAS part of another group COMPANY\APPUSERS, but the colleague was not.
3. COMPANY\APPUSERS had a blank default database under Login Properties. I changed it to master.
4. It works FINE now without specifying default db manually.
This raises couple questions:
1. What does the GroupID column in profiler mean ?
2. If a user is part of 2 different groups, which one's permissions are used?

1) I'm not seeing GroupID but you can see the list of columns available for traces and their definition if you look sp_trace_setevent up in BOL.

2) It uses a union of all of the different permissions a login/user has been granted through any group or as an individual. So if you are granted read access to tableA from one group and write access to tableA from another you will have both read and write access to tableA. However unless you are dbo of the database in question, or sysadmin of the server any DENY will always trump any grant.

So in the example I just gave if you are a member of a 3rd group that denys read/write access to tableA then you do not have read/write access to that table.

Hope that helps

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

sestell1 SSChampion Points: 10230 More actions · Answer 4

Hmm, that is very interesting that the trace showed the requested database as 'master' even though the login failed because of the default database. I have no idea how SQL Server determines which database to use as the default in this case!

The default database will be blank if it was set to a database that is no longer available.

Using groups as login accounts can get complicated and is hard to trace. I tend to avoid it for those reasons and set up explicit accounts and use roles for managing permissions.

sqld-_-ba SSCarpal Tunnel Points: 4365 More actions · Answer 5

Kenneth.Fisher (9/12/2012)
1) I'm not seeing GroupID but you can see the list of columns available for traces

It shows up if you select 'Show All Columns' in Events Selection Tab of Profiler (in sql2008 r2 atleast).

and their definition if you look sp_trace_setevent up in BOL.

http://msdn.microsoft.com/en-us/library/ms186265.aspx - GroupID is missing from their description (as mentioned in the comments)

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 6

waeva (9/12/2012)
Kenneth.Fisher (9/12/2012)
1) I'm not seeing GroupID but you can see the list of columns available for traces
It shows up if you select 'Show All Columns' in Events Selection Tab of Profiler (in sql2008 r2 atleast).
and their definition if you look sp_trace_setevent up in BOL.
http://msdn.microsoft.com/en-us/library/ms186265.aspx - GroupID is missing from their description (as mentioned in the comments)

I've seen the column in the traces before, I just didn't see it in the column definitions in BOL. Which I have to say rather surprised me, well, maybe not.

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

ScottPletcher SSC Guru Points: 101119 More actions · Answer 7

1. What does the GroupID column in profiler mean ?

GroupID as classified by the Resource Governor for the current session.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".