November 15, 2017 at 3:17 am
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
November 15, 2017 at 3:20 am
Check the properties of the login. What is its default database. Now, does the login have access to that database?
John
November 15, 2017 at 4:06 am
John Mitchell-245523 - Wednesday, November 15, 2017 3:20 AMCheck 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.
November 15, 2017 at 4:11 am
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
November 15, 2017 at 4:14 am
coolchaitu - Wednesday, November 15, 2017 4:06 AMJohn Mitchell-245523 - Wednesday, November 15, 2017 3:20 AMCheck 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.
Larnu.uk
November 15, 2017 at 4:33 am
Thom A - Wednesday, November 15, 2017 4:14 AMAD 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
November 15, 2017 at 4:47 am
John Mitchell-245523 - Wednesday, November 15, 2017 4:33 AMAre 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 = JohnYou 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.
Larnu.uk
November 15, 2017 at 7:08 am
Thom A - Wednesday, November 15, 2017 4:47 AMJohn Mitchell-245523 - Wednesday, November 15, 2017 4:33 AMAre 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 = JohnYou 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.
November 15, 2017 at 7:13 am
coolchaitu - Wednesday, November 15, 2017 7:08 AMThom A - Wednesday, November 15, 2017 4:47 AMJohn Mitchell-245523 - Wednesday, November 15, 2017 4:33 AMAre 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 = JohnYou 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
November 15, 2017 at 7:44 am
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.
November 15, 2017 at 10:08 am
anthony.green - Wednesday, November 15, 2017 7:13 AMcoolchaitu - Wednesday, November 15, 2017 7:08 AMThom A - Wednesday, November 15, 2017 4:47 AMJohn Mitchell-245523 - Wednesday, November 15, 2017 4:33 AMAre 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 = JohnYou 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
The individual login belongs to several groups(about 50 groups). How do i proceed?
November 15, 2017 at 10:19 am
Have you followed this up by checking the default database for those AD groups?
November 15, 2017 at 10:25 am
coolchaitu - Wednesday, November 15, 2017 3:17 AMGood 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
Or , may be the database itself is deleted or you are trying to connect to different server where this database doesn't exist.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply