SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


"Cannot open user default database. Login failed"


"Cannot open user default database. Login failed"

Author
Message
coolchaitu
coolchaitu
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4345 Visits: 1388
Good Morning Experts,

User received the below error while trying to connect to an instance

"Cannot open user default database. Login failed"

I checked and found that the user is a member of several AD Groups. Could you please advise on how to fix this
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79933 Visits: 17915
Check the properties of the login. What is its default database. Now, does the login have access to that database?

John
coolchaitu
coolchaitu
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4345 Visits: 1388
John Mitchell-245523 - Wednesday, November 15, 2017 3:20 AM
Check the properties of the login. What is its default database. Now, does the login have access to that database?

John

There are no individual logins. All are AD groups.

John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79933 Visits: 17915
Then you'll need to do a bit of investigation. Run xp_logininfo 'DomainName\GroupName', 'members' for each to group to find out which group(s) the user is a member of. That'll only work if the user's membership isn't nested in a subgroup, so if you don't find the user, ask your AD admin to check what groups the user is a member of.

John
Thom A
Thom A
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45729 Visits: 15539
coolchaitu - Wednesday, November 15, 2017 4:06 AM
John Mitchell-245523 - Wednesday, November 15, 2017 3:20 AM
Check the properties of the login. What is its default database. Now, does the login have access to that database?

John

There are no individual logins. All are AD groups.

AD Groups can't have default databases assigned, thus logins connecting via them will default to master. This either means that the person connecting is specifying a default database in their connection string they do not have access to, or their account has their own login on the SQL Server, which has a default database that (again) they do not have an attached user account for.



Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79933 Visits: 17915
Thom A - Wednesday, November 15, 2017 4:14 AM
AD Groups can't have default databases assigned, thus logins connecting via them will default to master. This either means that the person connecting is specifying a default database in their connection string they do not have access to, or their account has their own login on the SQL Server, which has a default database that (again) they do not have an attached user account for.

Are you sure about that, Thom? It's not documented on the CREATE LOGIN page, and I just successfully ran this command on a SQL Server 2014 server:

CREATE LOGIN [DomainName\GroupName] FROM WINDOWS WITH DEFAULT_DATABASE = John


You may be confusing this with default schemas not being able to be assigned to users mapped to logins that are Windows groups.

John

Thom A
Thom A
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45729 Visits: 15539
John Mitchell-245523 - Wednesday, November 15, 2017 4:33 AM

Are you sure about that, Thom? It's not documented on the CREATE LOGIN page, and I just successfully ran this command on a SQL Server 2014 server:

CREATE LOGIN [DomainName\GroupName] FROM WINDOWS WITH DEFAULT_DATABASE = John


You may be confusing this with default schemas not being able to be assigned to users mapped to logins that are Windows groups.

John

Yep, sorry! You're right. Clearly brain not engaged yet!



Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
coolchaitu
coolchaitu
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4345 Visits: 1388
Thom A - Wednesday, November 15, 2017 4:47 AM
John Mitchell-245523 - Wednesday, November 15, 2017 4:33 AM

Are you sure about that, Thom? It's not documented on the CREATE LOGIN page, and I just successfully ran this command on a SQL Server 2014 server:

CREATE LOGIN [DomainName\GroupName] FROM WINDOWS WITH DEFAULT_DATABASE = John


You may be confusing this with default schemas not being able to be assigned to users mapped to logins that are Windows groups.

John

Yep, sorry! You're right. Clearly brain not engaged yet!


There are no individual logins. Individual Logins are added to AD groups. These groups are added as server principals.
anthony.green
anthony.green
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62610 Visits: 8594
coolchaitu - Wednesday, November 15, 2017 7:08 AM
Thom A - Wednesday, November 15, 2017 4:47 AM
John Mitchell-245523 - Wednesday, November 15, 2017 4:33 AM

Are you sure about that, Thom? It's not documented on the CREATE LOGIN page, and I just successfully ran this command on a SQL Server 2014 server:

CREATE LOGIN [DomainName\GroupName] FROM WINDOWS WITH DEFAULT_DATABASE = John


You may be confusing this with default schemas not being able to be assigned to users mapped to logins that are Windows groups.

John

Yep, sorry! You're right. Clearly brain not engaged yet!


There are no individual logins. Individual Logins are added to AD groups. These groups are added as server principals.

You will need to find which group the individual login belongs to as the starting point (xp_logininfo 'DomainName\GroupName', 'members')
Then find that particular group and see what the default database is. If it is anything that's not master, ensure that database is present and correct




How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Bill Talada
Bill Talada
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6714 Visits: 2296
I get the list of domain groups for a domain user by opening a cmd window and executing:
net user /domain theirname

Also, I generally create a Startup database and assign all logins to that as a default. It prevents people from creating junk objects in master. It is also easy to check logins work without checking the status of all databases the login may use. This technique also forces applications to be responsible for setting a database after connecting and "knowing" which database they are currently working against.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search