[SOLVED] SQL user in linked table

I have a problem in the SQL Server option. For an integration piece that I just finished I have views in my Navision database into another database, and Navision tables that link to the views. The problem I have is that I have assigned access to my database users to both databases (even as much as db_owner), but the only way I can make it work is to add these users to the system administrators. You can imagine that this is not an optimal situation for my customer, they do not want to set all their database users up as sys admins. The error that I get is: The following SQL Server error(s) occurred while accessing the MyTable table: 916,“08004”,[Microsoft][ODBC SQL Server Driver][SQL Server]Server user ‘johnny’ is not a valid user in database ‘MyDatabase’. Does anybody here know how I can give these users access to these views without having to give them system admin rights?

Hi Danniel, I had the same problem a couple of years back, ad started a thread here, I kept changing stuf accordig to the advise given, but I never really found put what fixed it. If you search here for Linked Tables SQL, permission something like that you will find it. I do know it wasn’t the security sync thing, it was something else. Also I was not adminstering the SQL db, just feeding the Admin with ideas until it eventually worked. It might have been something like giving the Navision db_owener permission to that other server, or something to that effect. Sorry to be so vaigue, but maybe it will promt one of our SQL experts to remember what he told me to do then.

What a little pressure from a customer can do to my willingness to read all articles in my search results ;). Alright, I found a post by Robert Cinkaid that gave me at least enough to have access to the data without system administrator rights, which is simply by granting the user db_owner in the Navision database. Read the thread here. I have more questions about the db_owner role, but I’ll put that in a new thread. This one can be closed as far as I’m concerned.

Moved to SQL Forum.

When using Application Roles access to other databases is granted thru the guest account. Assign the appropriate access to the guest account of the other database. Assigning the DB_Owner role grants too much permission. A member of DB_Owner is granted complete access to all objects in the database. This includes total data access (read, write, delete) along with the ability to delete,create, or modify objects.