I need to create an user on SQL Server 2005 read-only, meaning only able to run a stored procedure. This stored procedure is accessing a SQL Server 2000 database in order to retrieve part of the data.
I'm getting this error:
Access to the remote server is denied because no login-mapping exists.
I added the local user to the user mapping table on Linked Server / Security, assigning the remote user and password. I've also specify "for login not defined above..." another valid user id e password but it still doesn't work.
If I assign the "sysadmin" role to the "read-only" user, everything works, this leads me think this is a security issue but I don't know how to fix it, I run out of idea. I don't want regular users to access as sysadmin, I want to control what they can do, can you please help?