Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2005-05-26 : 12:08:09
|
| I am in the process of setting up an user for her to connect to our testing server.She is in the user group that was authenticated to the live server. I thought I copied all the users and roles from live to test server.Now when I tried to add a system DSN from her machine, neither the windows nor sql authentication connect successfully. The message said, “Cannot open user default database, login failed”. One thing unusual is that the server was listed twice on the server dropdown box, while all other servers are listed only once.Of cause, I can connect to the test server from my workstation.I'd appreciate any help! |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-05-26 : 13:38:16
|
| sounds like the default database name for that user in the prod environment isn't available in the test environment.try running this from a QA window: (replacing User and Database, obviously)sp_defaultdb @loginame = '<User>', @defdb = '<Test Database>'Be One with the OptimizerTG |
 |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2005-05-26 : 15:02:02
|
Now after I re-created the user in EM, I could set up a system DSN using sql authentication, but still unable to add a windows authentication DSN. She and I are on the same windows domain. I tried on several other users. Some got in, others didn't.  |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-05-26 : 15:57:51
|
| can those users connect to the db server using Query Analyzer with windows authentication?Be One with the OptimizerTG |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2005-05-26 : 16:09:58
|
| I found out this will add a windows user.sp_grantlogin 'windowsDomainName\UserName'Then I could set up the DSN.They don't have QA installed, so I cannot test as have suggested, but thanks! |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-05-26 : 22:22:14
|
quote:
Originally posted by Hommer
I found out this will add a windows user.sp_grantlogin 'windowsDomainName\UserName'Then I could set up the DSN.They don't have QA installed, so I cannot test as have suggested, but thanks!
sp_grantlogin will only grant the windows user but will not provide access to the default database, use sp_grantdbaccess to allow the user to connect to the database--------------------keeping it simple... |
|
|
|
|
|
|
|
|
how to authenticate an user