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
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8871 Visits: 1643
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 (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)

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

John
coolchaitu
coolchaitu
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8871 Visits: 1643
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 (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)

Group: General Forum Members
Points: 135316 Visits: 19489
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 Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86830 Visits: 22264
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 (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)SSC Guru (135K reputation)

Group: General Forum Members
Points: 135316 Visits: 19489
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 Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86830 Visits: 22264
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
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8871 Visits: 1643
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 (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)

Group: General Forum Members
Points: 103190 Visits: 8698
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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10836 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