SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
paulc.byrum
paulc.byrum
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 116
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
OTF
SSChasing Mays
SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)

Group: General Forum Members
Points: 636 Visits: 4128

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
paulc.byrum
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 116
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
OTF
SSChasing Mays
SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)

Group: General Forum Members
Points: 636 Visits: 4128
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
OTF
SSChasing Mays
SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)

Group: General Forum Members
Points: 636 Visits: 4128

'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
paulc.byrum
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 116
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
paulc.byrum
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 116
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
OTF
SSChasing Mays
SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)

Group: General Forum Members
Points: 636 Visits: 4128

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
vidya_pande
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1283 Visits: 242
Please check the the login still exists in the database as a user.



Perry Whittle
Perry Whittle
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28007 Visits: 17351
what info does the event log show?

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
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