cannot connect to the sql server database

Hello Experts,

I am unable to connect to the SQL Sever 2005 database for the user "GB Service". Although the user credentials are correct. Please can someone advise how to fix this error

===========================

Cannot connect to GB-SERVER

===========================

Cannot open user default database. Login failed.
Login failed for user 'GB Service'. (.Net SqlClient Data Provider)
 

thanks for your help
kay
learningnetAsked:
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.

DBAduck - Ben MillerPrincipal ConsultantCommented:
Having a space in your username is not helping you.  I am not sure how easy it is for you to change the username, but you should look at that.  

But in this specific case, the error is associated with the fact that the default database specified for this user is either gone and does not exist, or that the user does not have a user mapping for that database.

So you need to look at the properties of the GB Service user and make sure that the default database is correct and that this login has access to the database specified.
0
Heathcliff74Commented:
Every user has a default database assigned to log into. This user has assigned a database that is somehow not accessible. Login with an administrative account to this SQL Server Instance, using the SQL Server Management Studio and change the dedault database for this account:

After being logged into Management Studio expand "Security" and then "Logins".
Double-click on the user "GB Service".
On the "General" page change the "Default database".
Click OK.

That should do it. Good luck.
0
learningnetAuthor Commented:
hello dbaduck

thanks for your comment, i will make sure in the future i do not have spaces in the names.

i do not want to change this user as most of the .net web services are connecting to the DB using this user credentials, it works fine on live server its on the development i am having this problem

yes you might be right about not having default database specified for this user

please can you advise how to fix this?

thanks

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DBAduck - Ben MillerPrincipal ConsultantCommented:
The instructions are in the post below mine.  In the properties of the Login in Security in the management tools, you will see the default database and you need to make sure that it is a database that the user has access to.
0
learningnetAuthor Commented:
Heathcliff74
thanks for your help too, i have followed your advise and to my surprise this user has already got default database set
i dont know why is it not connecting...

please advise
0
Heathcliff74Commented:
The default database might be deleted, corrupted, or unauthorized. You might want to repair that database, select another database or authorize that database.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
Yes, but make sure that in the User Mapping that the login has a user in the database that it has as the default.

Click on User Mapping while in the properties of the Login.  Then make sure that this login is a user in the default database.
0
learningnetAuthor Commented:
well when i changed the database name to master it worked ...

does the default database always need to be master?

i need to check whether the connection string in my .net application works now..

will let you know ...

thanks
0
Heathcliff74Commented:
Try to connect to that database with another (administrator) account. If it works, then the database is ok. If it doesn't work the database is corrupt or deleted. If you can login with an admin account, the you should authorize the "GB Service" for that database.
0
Heathcliff74Commented:
Changing to "master" is a good solution. You should only change that if you have very specific needs for that and then you should also be sure the database is valid and authorized for that used. For now, I think you should just leave it at "master" database.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
You can leave it as master, but it is more correct to put the users default database to the one that they access.  So if the login only uses one database then it should have that database as the default database.  But for now it should work as master, and you can determine what the problem is.
0
learningnetAuthor Commented:
guys
i actually wanted the giftrepXXXX to be my default database for the gr service user

when i tried mapping it i got the following  error

please advise
===================================
 
Create failed for User 'GR Service'.  (Microsoft.SqlServer.Express.Smo)
 
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+User&LinkId=20476
 
------------------------------
Program Location:
 
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
   at Microsoft.SqlServer.Management.Smo.User.Create()
   at Microsoft.SqlServer.Management.SqlManagerUI.CreateLoginData.LoginPrototype.ApplyDatabaseRoleChanges(Server server)
   at Microsoft.SqlServer.Management.SqlManagerUI.CreateLoginDatabaseAccess.OnRunNow(Object sender)
 
===================================
 
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
 
------------------------------
Program Location:
 
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplFinish(StringCollection createQuery, ScriptingOptions so)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
 
===================================
 
User, group, or role 'GR Service' already exists in the current database. (.Net SqlClient Data Provider)
 
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15023&LinkId=20476
 
------------------------------
Server Name: GR-SERVER-01
Error Number: 15023
Severity: 16
State: 1
Line Number: 1
 
 
------------------------------
Program Location:
 
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

Open in new window

0
DBAduck - Ben MillerPrincipal ConsultantCommented:
Go into a query window as an admin and change to that database and run this :

EXEC sp_change_users_login 'Report'

If the GR Service is listed in that report then you can link it up.

EXEC sp_change_users_login 'Update_One', 'GR Service', 'GR Service'

Then change the default database for the user to that database.
0
Heathcliff74Commented:
Right-click on the database and open "properties".
Go to the "Permissions" page.
Click "add"
Click "Browse"
Select "GR Service"
Click OK
Grant all desired access, but at least "Connect".
Click OK
0
learningnetAuthor Commented:
hello dbaluck
yes i can see the GR Service listed

if the database i wanted to connect is "giftrepub" then what should this statement be?

EXEC sp_change_users_login 'Update_One', 'GR Service', 'GR Service'

thanks
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
You should be in the database, and then execute the statement you have.

EXEC sp_change_users_login 'Update_One', 'GR Service', 'GR Service'
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
learningnetAuthor Commented:
thanks a million folks !

i am now able to login using the user i want with a desired default database...

ee rocks !

cheers !!!
0
Heathcliff74Commented:
You're welcome!
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.

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.