SQL User in Linked Table

DenSter

I have 2 databases on my SQL Server, one of which is a Navision database. For this integration piece that I wrote I created views into the other one in the Navision database, and created linked tables to get to the data. Everything works fine, the users accepted the solution, so today we were ready to take it live.

Everything installed, views created, the sys admin at the customer was able to open the linked tables, so it all connects fine. Then, we added the first user to the system (to both databases, just like in testing), and the first time try to access any of the data, they get a SQL error 'user X is not a valid user on the database'. I verified this with my own eyes, and the user exists in both databases. The only way that we could make this work is to make the user system administrator on the SQL Server.

Does anybody here know how I can give regular database users access through these views without giving them system admin rights?

ovicash

Did you gave acces on the table/view to the user ?
I mean in Sql Server Enterprise manager-> Views-> All Tasks-> Manage Permissions?
:?:

DenSter

I can't find views -> all tasks -> manage permissions. Please be more specific.

I don't think it is about permissions, for some reason it is not recognizing the user, so it is not even letting the credentials into the database. You first have to get into the database, and then after you are in, it worries about permissions to specific objects in the database. I need to know how to make SQL Server recognize the user without having to give it server admin rights.

DenSter

It can't be permissions. I set the user to db_owner, so that gives them all permissions. When I try to open the linked table in Navision it still gives me this message :

916,"08004",[Microsoft][ODBC SQL Server Driver][SQL Server]Server user 'johnny' is not a valid user in database 'MyDatabase'

Marije_Brummel

Did you had a look at

http://www.mibuso.com/forum/viewtopic.php?t=7706

Maybe it is the same problem?

DenSter

Thank you Mark, I had found the same article, but it was no help. I tried all those suggestions and I still get the error.

DenSter

Okay, I found an answer. Read all about it here. The answer was to simply grant db_owner to all database users in the Navision database, and take away system administrator. I'm still not completely satisfied though, because not all users should have to be db_owner to get access to data.

ovicash

DenSter wrote:

I can't find views -> all tasks -> manage permissions. Please be more specific.

I don't think it is about permissions, for some reason it is not recognizing the user, so it is not even letting the credentials into the database. You first have to get into the database, and then after you are in, it worries about permissions to specific objects in the database. I need to know how to make SQL Server recognize the user without having to give it server admin rights.

I meant: after you open a database in Enterprise Manager there are (diagrams, tables, views....). If you select a specific view you can find in properties -> all tasks-> manage permissions.

I don't know if it helps but I know that you must give permissions to a view if the user who wants to connect isn't a sys admin or db_owner

DenSter

Ah now I see what you mean, thanks for explaining . I tried the permissions on the view, and I even explicitly set the same permissions on the table itself, but as soon as I take away db_owner it says that the user is not a valid user in the target database.

ovicash

DenSter wrote:

Ah now I see what you mean, thanks for explaining . I tried the permissions on the view, and I even explicitly set the same permissions on the table itself, but as soon as I take away db_owner it says that the user is not a valid user in the target database.

And now a stupid question. I've seen it at a client

Does the database the option from Database->Atler->Options->Acces

"Memebers of db_owner, dbcreator or sysadmin"

Activated ? :whistle:

kine

May be that there is problem with the Shadow role (application role)... Navision create app role in the database which will be connecting... For each db is new app role created and granted permission for the DB.

Now, you have 2 DB and 2 App roles. You connect view in one DB to table in another DB. You start Navision, it connect with the app role to the 1st DB and try to read data from 2nd DB - no permission. Why? Because the app role have no rights to read data from 2nd DB...

May be that problem is there...

If the user is db_owner, he will be connected as db_owner and not as application role used by Navision... (it has higher priority)