error 4064

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.
LVL 5
25112Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tony303Commented:
Hi,

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>?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chris LuttrellSenior Database ArchitectCommented:
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)
Begin
	select top 1 @uName=UserName from @u
	IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = @uName)
	BEGIN
		select @ss='DROP USER '+ UserName from @u where UserName = @uName
		print @ss + ' because of no Login Account'
		--exec sp_executesql @ss 
	END
	ELSE
	BEGIN
		select @ss='sp_change_users_login Auto_Fix, '+ UserName from @u where UserName = @uName
		print @ss
		exec sp_executesql @ss 
	END
	delete	from @u where UserName = @uName
End

exec sp_change_users_login Report

Open in new window

0
Anthony PerkinsCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.