Post reply

default database for a login

  • 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

  • August 2, 2012 at 7:34 pm

    #1521200

    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.

  • August 3, 2012 at 12:54 am

    #1521252

    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

  • August 3, 2012 at 7:11 am

    #1521367

    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.

  • August 3, 2012 at 8:32 am

    #1521434

    Can you drop & recreate the group Login?

  • August 3, 2012 at 9:46 am

    #1521486

    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

  • August 3, 2012 at 9:54 am

    #1521491

    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

  • August 3, 2012 at 11:02 am

    #1521531

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

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

  • August 3, 2012 at 11:20 am

    #1521540

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

    Pooyan

  • August 3, 2012 at 11:26 am

    #1521541

    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

  • August 3, 2012 at 11:32 am

    #1521544

    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!

  • August 3, 2012 at 11:47 am

    #1521555

    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

  • August 3, 2012 at 12:01 pm

    #1521564

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

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • August 3, 2012 at 12:03 pm

    #1521565

    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

  • August 3, 2012 at 12:05 pm

    #1521569

    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

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

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