Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Login failed for user - Microsoft SQL Server, Error: 4064 Expand / Collapse
Author
Message
Posted Thursday, March 25, 2010 4:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 06, 2010 11:55 AM
Points: 23, 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




Post #890300
Posted Thursday, March 25, 2010 4:59 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128

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.
Post #890306
Posted Thursday, March 25, 2010 5:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 06, 2010 11:55 AM
Points: 23, 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.

Post #890314
Posted Thursday, March 25, 2010 5:56 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128
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...
Post #890325
Posted Thursday, March 25, 2010 6:55 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128

'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.
Post #890336
Posted Friday, March 26, 2010 10:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 06, 2010 11:55 AM
Points: 23, 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?
Post #890821
Posted Friday, March 26, 2010 10:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 06, 2010 11:55 AM
Points: 23, 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?
Post #890839
Posted Friday, March 26, 2010 6:53 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128


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.
Post #891196
Posted Saturday, March 27, 2010 1:11 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 6:44 PM
Points: 891, Visits: 234
Please check the the login still exists in the database as a user.


Post #891329
Posted Sunday, March 28, 2010 5:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:15 PM
Points: 5,951, Visits: 12,822
what info does the event log show?

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

"Ya can't make an omelette without breaking just a few eggs"
Post #891437
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse