Login failed for user - Microsoft SQL Server, Error: 4064

  • paulc.byrum

    Right there with Babe

    Points: 757

    Hello,

    When I click on properties of a database, users, tables, or any object within SQL Server Management Studio, I get this error:

    Cannot show requested dialog.

    Cannot open user default database. Login failed.

    Login failed for user 'PBTF\bob'. (Microsoft SQL Server, Error: 4064)

    I have set the default database to the master, by selecting Options on the Connect to Server dialog and ran this command: ALTER LOGIN 'PBTF\bob WITH DEFAULT_DATABASE = master.

    SQL Server says:

    Cannot alter the login 'PBTF\bob', because it does not exist or you do not have permission.

    'PBTF\bob' account is in a security group, which has been added to SQL Server for Server Access. 'PBTF\bob' and the security group are not added to the database security. Security group has admin rights in SQL Server.

    Despite all this, I still experience the error. Any tips on how I can fix this?

    Thanks

  • OTF

    SSCertifiable

    Points: 6054

    Cannot show requested dialog.

    Cannot open user default database. Login failed.

    Login failed for user 'PBTF\bob'. (Microsoft SQL Server, Error: 4064)

    I think the error is what is: cannot open your default database.

    Does the default database still exist?

    You can probably go in via sqlCmd and change your default database.

    sqlcmd -E -d master

    then issue your alter login statement.

  • paulc.byrum

    Right there with Babe

    Points: 757

    The default database is master and it still exists.

    I get this when running the commands:

    C:\>sqlcmd -E -d master

    1> ALTER LOGIN pbtf\bob WITH DEFAULT_DATABASE = master

    2> go

    Msg 170, Level 15, State 1, Server THOTH, Line 1

    Line 1: Incorrect syntax near 'LOGIN'.

    I am logging in as bob with Windows Authenication. As mentioned before, bob's accont is in the Active Directory security group at the SQL Server level.

  • OTF

    SSCertifiable

    Points: 6054

    paulc.byrum (3/25/2010)


    The default database is master and it still exists.

    I get this when running the commands:

    C:\>sqlcmd -E -d master

    1> ALTER LOGIN pbtf\bob WITH DEFAULT_DATABASE = master

    2> go

    Msg 170, Level 15, State 1, Server THOTH, Line 1

    Line 1: Incorrect syntax near 'LOGIN'.

    I am logging in as bob with Windows Authenication. As mentioned before, bob's accont is in the Active Directory security group at the SQL Server level.

    I think the Incorrect Syntax error is because you have not delimited the login name, try [pbtf\bob].

    You should be able to change the default database for your login.

    That being said, I don't follow. If your default database is already master...

  • OTF

    SSCertifiable

    Points: 6054

    'PBTF\bob' account is in a security group, which has been added to SQL Server for Server Access.

    Sorry, I just noted your statement that you are in a group. Your default database should be set at group level. You may want to check if anyone else in the group is getting the same error and change the default database at the group level.

    Security group has admin rights in SQL Server.

    This, however, should mean that you have access to all the databases on the Server because sysadmin is mapped to dbo for all databases on the instance.

  • paulc.byrum

    Right there with Babe

    Points: 757

    I am trying with the security group name now.

    ALTER command says Line 1: Incorrect syntax near 'LOGIN'.

    C:\>sqlcmd -E -d master

    1> ALTER LOGIN [PBTF\Group - IT DB Admins] WITH DEFAULT_DATABASE = master

    2> go

    Msg 170, Level 15, State 1, Server THOTH, Line 1

    Line 1: Incorrect syntax near 'LOGIN'.

    1>

    Any suggestions?

  • paulc.byrum

    Right there with Babe

    Points: 757

    Perhaps, my problem is that I have several instanaces of SQL Server. They are:

    SQL Server 9.0.4035 (SharePoint instance)

    SQL Server 8.0.2055 (Standard edition)

    SQL Server 8.0.760 (MSDE)

    SQL Server 9.0.4053 (Express Edition)

    The instance with the problem is "SQL Server 9.0.4035 (SharePoint instance)".

    If I am using the sqlcmd, how do I ensure I am using the correct instance?

    Is the ALTER LOGIN command availble in this instance?

  • OTF

    SSCertifiable

    Points: 6054

    Is the ALTER LOGIN command availble in this instance?

    If you are getting the "Incorrect Syntax" error, You may want to double check the instance you are running the ALTER LOGIN command against. It is not available in Sql Server 2000.

    If I am using the sqlcmd, how do I ensure I am using the correct instance?

    Typing sqlcmd -? at the command prompt will list all the options/switches.

    You can specify a server to connect to using the -S switch (-S servername).

    I will suggest that you check with other users in the group to see if they are having similar problems before you change the default database.

  • vidya_pande

    SSCertifiable

    Points: 5275

    Please check the the login still exists in the database as a user.

  • Perry Whittle

    SSC Guru

    Points: 233794

    what info does the event log show?

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • paulc.byrum

    Right there with Babe

    Points: 757

    The login does exist in the security group.

    In the event log, the event occurs when the error message occurs in SQL Server Management Studio:

    The description for Event ID ( 18456 ) in Source ( MSSQL$MICROSOFT##SSEE ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer.

    I ran the following command and there were no errors. It seemed to work, but I tried viewing properties in Management Studio and the error still occurs:

    C:\>sqlcmd -E -d master -S THOTH\MICROSOFT##SSEE

    1> ALTER LOGIN [PBTF\Group - IT DB Admins] WITH DEFAULT_DATABASE = master

    2> go

    1>

  • OTF

    SSCertifiable

    Points: 6054

    What error do you get now? Is everyone in that group have the same error/problem when accessing Sql Server?

  • Johan Bijnens

    SSC Guru

    Points: 134265

    Keep in mind that if a use belongs to multiple groups, the default database setting cannot be used !

    Check the access paths and memberships:

    Use yourdb

    go

    -- how does this login get to the database (authority paths)

    EXEC master..xp_logininfo @acctname = 'mydomain\mylogin',@option = 'all'

    go

    -- who is member of this group

    EXEC master..xp_logininfo @acctname = 'mydomain\mygroup',@option = 'members'

    go

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • paulc.byrum

    Right there with Babe

    Points: 757

    When I run the following in SSMS, I get:

    -- how does this login get to the database (authority paths)

    EXEC master..xp_logininfo @acctname = '[PBTF\Group - IT DB Admins]',@option = 'all'

    go

    Results:

    Msg 15404, Level 16, State 11, Procedure xp_logininfo, Line 62

    Could not obtain information about Windows NT group/user '[PBTF\Group - IT DB Admins]', error code 0x534.

    -- who is member of this group

    EXEC master..xp_logininfo @acctname = '[PBTF\Group - IT DB Admins]',@option = 'members'

    go

    Results:

    The query executed successfully, but nothing was return. No rows.

  • paulc.byrum

    Right there with Babe

    Points: 757

    I took off the [] and got results. PBTF\bob is in the security group.

    account name type privilege mapped login name permission path

    ---------------------------------------------------------------------------------------

    PBTF\Group - IT DB Admins group admin PBTF\Group - IT DB Admins NULL

    account name type privilege mapped login name permission path

    --------------------------------------------------------------------------------------

    PBTF\bob user admin PBTF\bob PBTF\Group - IT DB Admins

    PBTF\AStan user admin PBTF\AStan PBTF\Group - IT DB Admins

    PBTF\AYalley user admin PBTF\AYalley PBTF\Group - IT DB Admins

    PBTF\GTrap user admin PBTF\GTrap PBTF\Group - IT DB Admins

    PBTF\PBrooks user admin PBTF\PBrooks PBTF\Group - IT DB Admins

    PBTF\SvcSPContent user admin PBTF\SvcSPContent PBTF\Group - IT DB Admins

    PBTF\SvcSPSearch user admin PBTF\SvcSPSearch PBTF\Group - IT DB Admins

    PBTF\SvcSPSQL user admin PBTF\SvcSPSQL PBTF\Group - IT DB Admins

    PBTF\SvcSQL user admin PBTF\SvcSQL PBTF\Group - IT DB Admins

    PBTF\SvcSQLRS user admin PBTF\SvcSQLRS PBTF\Group - IT DB Admins

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

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