Link to home
Start Free TrialLog in
Avatar of Dale Fye
Dale FyeFlag for United States of America

asked on

SQL Server Error: 4060

A client has asked me to modify his Access application which points to his SQL Server.  He backed up the database and sent it to me, and I was able to restore from backup.  But when I try to establish a DSN-less connection to the database, with a specific UserID, which has permissions to a variety of objects on the original database, I get the message posted above

SQL Server Error: 4060
Server rejected the connection; Access to selected database has been denied.

Do I need to remove the version of the UserID (SQL Server Authentication) from my local server and recreate it with the appropriate permissions?  or is there a way to script the permissions of a specific user on the original SQL Server (and database), and then add those permissions to my local server? and the database which was restored?
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

What's the default database of that login in the SQL Server instance?
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Dale Fye

ASKER

I meant to post that as the owner, I'm able to create the dsn-less connections using ";Trusted_Connection=Yes",

But this does not work:

"ODBC;Driver=SQL Server Native Client 10.0;Server=(local);Database=FlexReport;UID=myUID;PWD=myPWD"

The default database for "myUID" is FlexReport, but it is my understanding that user permissions on the server (and maybe the database) don't actually get restored from a db backup, is that correct?  After the restore operation, there was a database userID = "myUID", but there was not a server level login for that user.

Ste5an,  would it not be simpler to just script the user from the original database, and run those scripts on my server?  If so, do I have to script the user login at the Server level, and then again at the database level?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Vitor,

Actually, it is not an AD userID.  The client installs machines at user sites and installs SQL Server with a common userID and password across all clients.  So I specifically need the UID and permissions from the original server/database.  How do I get those and install them on my local instance of SQL Server?

Dale
Your comments are contradictory then.
First you said: "I'm able to create the dsn-less connections using ";Trusted_Connection=Yes","

You only use TrustedConnection when connecting with an AD user.
Vitor,

Sorry, my bad.  When I used Trusted_Connection=Yes, it was using my personal AD account, and is therefore using Windows Authentication, which worked fine.  But when using the SQL Server Authentication with a userid and password it was returning the error mentioned above.

Dale
Thanks, guys.

Once I scripted the login properties of the generic user from the original database and applied them my server the login process worked properly.