default database for a login

Question

default database for a login

pooyan_pdm

Hall of Fame

Points: 3007
More actions
August 2, 2012 at 4:40 pm

#265078

Hi there
some body dropped a database which was the default database for a specific login(a windows group login with sysadmin server role) .
after that, users can't connect with that login anymore although the default database is now set master.I dropped the login and recreated that (even with a new name) and set the default database to master but again users can't connect via that login unless they specify a database and not using the default database.when I add any member of that windows group directly as a new login that member can connect normally using default database!! any idea??
Pooyan

Viewing 15 posts - 1 through 15 (of 25 total)

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

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 1

pooyan_pdm (8/2/2012)
Hi there
some body dropped a database which was the default database for a specific login(a windows group login with sysadmin server role) .
after that, users can't connect with that login anymore although the default database is now set master.I dropped the login and recreated that (even with a new name) and set the default database to master but again users can't connect via that login unless they specify a database and not using the default database.when I add any member of that windows group directly as a new login that member can connect normally using default database!! any idea??

I am not sure if I understand your scenario correctly, please break it down.

Thanks,

TA

Regards,
SQLisAwe5oMe.

pooyan_pdm Hall of Fame Points: 3007 More actions · Answer 2

There was a database named X . This was the default database for a login named Y . This login was a windows group login. User Z is a windows user and a member of that windows group.The database X was dropped and the default database for the login Y was changed to master . The problem is that user Z is not able to connect with the login Y anymore. But when a login is created directly in sql server for the windows user Z the user Z can connect to the database. Why??

Pooyan

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 3

pooyan_pdm (8/3/2012)
There was a database named X . This was the default database for a login named Y . This login was a windows group login. User Z is a windows user and a member of that windows group.The database X was dropped and the default database for the login Y was changed to master . The problem is that user Z is not able to connect with the login Y anymore. But when a login is created directly in sql server for the windows user Z the user Z can connect to the database. Why??

Did you map the user Z with login Y after recreating the login? I am not sure about the last sentence you mentioned.

Regards,

TA

Regards,
SQLisAwe5oMe.

Gazareth One Orange Chip Points: 27737 More actions · Answer 4

Gazareth

One Orange Chip

Points: 27737

August 3, 2012 at 8:32 am

#1521434

Can you drop & recreate the group Login?

pooyan_pdm Hall of Fame Points: 3007 More actions · Answer 5

I tried that but it didn't work. Even created a new windows group and created a new login in sql for that same problem!!

Pooyan

pooyan_pdm Hall of Fame Points: 3007 More actions · Answer 6

I created the login with sysadmin server role. With the last sentences I mean for example when I add a login for win user mydomain\z the z user can connect with the default database but when I drop this login and keep the login Y , although this is a win group login and Z is a member of this group , user Z is not able to connect with the default database setting

Pooyan

David Webb-CDS SSCoach Points: 17398 More actions · Answer 7

What's the actual error message that used Z is getting?

And then again, I might be wrong ...
David Webb

pooyan_pdm Hall of Fame Points: 3007 More actions · Answer 8

Error 4064 'cannot open user default database' I ran checkdb on master , every thinh seems to be working properly over there

Pooyan

David Webb-CDS SSCoach Points: 17398 More actions · Answer 9

Does the error log show the failed login? Does the group have at least 'public' role in master?

And then again, I might be wrong ...
David Webb

Lowell SSC Guru Points: 323518 More actions · Answer 10

don't you just need to assign a new default database to the login in question?

assigning it master as the default is what i typically do.

ALTER LOGIN login_name WITH DEFAULT_DATABASE = master

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pooyan_pdm Hall of Fame Points: 3007 More actions · Answer 11

I had done it at the first place the default database is already set to master even when I set the default database to any other database again the users within that group are not able to connect with default database. if they change the database for their connection string (or in the Option setting in SSMS) to explicitly specify 'Master' they can connect but not with full permission(sysadmin).

Pooyan

Steve Jones - SSC Editor SSC Guru Points: 736445 More actions · Answer 12

Just a guess, but has the instance been restarted? It sounds like you've done things right, but perhaps something is stuck?

pooyan_pdm Hall of Fame Points: 3007 More actions · Answer 13

No interestingly it is not logged!! bu when i drop the login for user Z and keep the login for the group the error

is changed:

Login failed for user 'MyDomain\Z'. Reason: Failed to open the database specified in the login properties.

and

Error: 18456, Severity: 14, State: 40.

Pooyan

pooyan_pdm Hall of Fame Points: 3007 More actions · Answer 14

ya I restarted the SQL Sever service . It's so confusing for me because even with creating a new win group login and adding the user Z to that group and assigning this group to sysadmin server role again I face the same problem!!

Pooyan