Hi
I have 2 linked servers that link back to a common server. I have one app at this time that uses the linked server to retrieve data from them. I have a few questions in regards to the above error message.
1. When the linked server was installed I believe that the domain/Administrator was set as dbo. Can this be changed? Reasoning, If I use the app under the administrator account, I can't connect. I get the "Login Failed for User" error. Investigating the error, I noticed that the admin account was not linked to a DB. When I tried to add the admin account to the list of users for the database, I would get the following error:
Create failed for User 'Domain\Administrator'. (Microsoft.SqlServer.Smo)
-----------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The login already has an account under a different user name. (Microsoft SQL Server, Error: 15063)
2. Is there a way to setup a common user profile that anyone using my app could be assigned that would not require me to add the app user to the remote servers? Would a Service Principal Name help provide that feature? Ideally, I'd like the user to login to there machine as usual, but anytime they make a call to the remote db through my app, I'd like the system to use the User Profile to allow them through. I'm hoping to avoid the need to add new users to all three machine every time I have a new user.
Thanks for any help.