error 4064

Posted on 2013-09-10
Medium Priority
Last Modified: 2013-09-12
for some users, the connection in SSMS fails:

Cannot open user default database. Login failed.
 Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)

why does not the connection like 'default' chosen? and this happens only for some users? why?

this user has permission to the database in question and that database has a proper owner. (no NULL)

when the user chooses a different database as default, then the connection goes through. but it is a pain to change everytime trying to connect SSMS.
Question by:25112
LVL 12

Accepted Solution

Tony303 earned 1600 total points
ID: 39481494

When you change the "Connect to Database:" field in Connection Properties on my 2005 and 2008 SSMS the value stays. (IE, the database selected shows not default).

So, I am thinking you only have to set up a user once on their machine to have these values write to registry and will be good to go for that user in future.

Are you saying the database selected as the default is having the login problem, or is it showing <default>?
LVL 27

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 400 total points
ID: 39481790
You have probably restored the DB or something and got the users SID out of sorts with what is in that database.  Try running this to see if any users login is not right on the database:
exec sp_change_users_login Report

Open in new window

You will get something like this for any users whose SID is wrongResult_PicI have this code saved and run it everytime I have to pull back a copy of our Production database and restore it on top of a DEV or STG instance on a different server.  It resets the users that get out of whack.
declare @U Table (UserName nVarchar(100))
declare @uName nvarchar(100), @ss nvarchar(500)

exec sp_change_users_login Report

insert into @u
--exec sp_change_users_login Report
	select UserName = name 
	from sysusers
	where issqluser = 1 
	and   (sid is not null and sid <> 0x0)
	and   (len(sid) <= 16)
	and   suser_sname(sid) is null
	order by name

select UserName from @u

while exists (select * from @u)
	select top 1 @uName=UserName from @u
	IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = @uName)
		select @ss='DROP USER '+ UserName from @u where UserName = @uName
		print @ss + ' because of no Login Account'
		--exec sp_executesql @ss 
		select @ss='sp_change_users_login Auto_Fix, '+ UserName from @u where UserName = @uName
		print @ss
		exec sp_executesql @ss 
	delete	from @u where UserName = @uName

exec sp_change_users_login Report

Open in new window

LVL 75

Expert Comment

by:Anthony Perkins
ID: 39481810
I have this code saved and run it everytime I have to pull back a copy of our Production database and restore it on top of a DEV or STG instance on a different server.
You may want to consider resetting all your SID's in all environments to be the same, that way they are always in sync.

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question