Table Valued Function and Linked Server
-
I know we cannot directly call a Table Valued Function on a linked server but my issue is a little different. I have a table valued function that queries tables on a linked server. When I run a SELECT against the table valued function, I get an error that states:
Msg 916, Level 14, State 1, Line 4
The server principal "myuser" is not able to access the database "LinkedDB" under the current security context.I have looked, and looked, and I find all kinds of references to calling a TVF on a linked server not being allowed but I cannot find this situation. I believe it may have something to do with scope but I am not sure. Here is more info on my setup:
I have my user, "myuser", that is setup in the db_datareader role in "MyDB". I have a linked server call "MyLinkedServer" that connects to the server containing "LinkedDB". The linked server is setup to use a SQL login that has access to only the tables referenced in my TVF. So, no matter who uses the linked server, it always uses that SQL login. I have verified that the linked server's login does exist and does have the necessary permissions. This is a simple example of my TVF:
CREATE FUNCTION dbo.MyTVF()
RETURN TABLE
AS
RETURN
SELECT *
FROM
CREATE FUNCTION dbo.MyTVF()
RETURN TABLE
AS
RETURN
SELECT *
FROM MyLinkedServer.LinkedDB.dbo.TableA"[/code]As expected, if I set the login "myuser" as a user in LinkedDB, it works. That makes me think the TVF is running in a different scope and maybe causing the problem. I still thought that, since the linked server is using a specified login, it would work regardless of who was running the TVF.
Can anyone explain to me what is happening or what I am missing?
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011 -
How is the linked server configured 'to use a specified login'?
If you are adding a login named 'myuser' to MyLinkedServer - and then adding that as a user in 'LinkedDB' - then it would appear that the linked server is configured as 'Be made using the login's current security context', or you have a local login mapped to a remote user login.Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Can you connect to the remote server as the user?
If so, can you run the query under those credentials?
USE LinkedDB
GO
SELECT *
FROM dbo.TableA -
Please excuse my delay in responding and my somewhat incomplete description of the situation. I put this post together rather hastily and did not do a good job of proofreading.
First, Jeffrey Williams 3188, my linked server security is setup using the "Be made using this security context" setting and I provide a remote login, with the appropriate password. I have verified that that remote login is valid and has all of the permissions needed in the linked database. I set it up this way so that anyone using the linked server would automatically have the permissions needed for the linked server.
In response to petrimd, when I setup the actual user's login in the linked database, I can run the query you described. As mentioned above, though, I am using a remote login for my linked server so that I don't have to setup my actual user in the linked database. I can, while not running the TVF, run a query in "MyDB" and directly query the appropriate tables in "LinkedDB". It is only when I try to use the TVF that I have problems.
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011 - hawg - Monday, April 2, 2018 6:48 AM
I'm wondering if the users just don't have permission to "impersonate" the specified login. I've never seen it be a good idea to have a TVF make use of a Linked Server table, because the Linked Server then has to deliver that entire table across the network before that query can be satisfied. Unless it's a VERY small table, that can take a while. Unless OPENQUERY or OPENROWSET is used to pass a query through to the linked server that will significantly reduce the number of rows being returned, use of Linked Servers in TVFs is just not going to perform well.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - hawg - Monday, April 2, 2018 6:48 AM
HAWQ,
Above you stated you already tested that the remote login is valid and has all the permissions. Security can be easily misunderstood at times, but the message text from your original post indicates the very problem you are experiencing is due to permissions:
Msg 916, Level 14, State 1, Line 4
The server principal "myuser" is not able to access the database "LinkedDB" under the current security context.
It seems like you already have a good understanding of what needs to occur, but something isn't jiving well. When I asked if you had logged into the remote server with the credentials, I was asking if you had logged into the remote server with the credentials you are passing through from the source server and tried to run the query; this guy
What happens when you connect to the remote server in SSMS using the login and pass from this menu and run the SELECT query?
- petrimd - Monday, April 2, 2018 9:27 AMhawg - Monday, April 2, 2018 6:48 AM
HAWQ,
Above you stated you already tested that the remote login is valid and has all the permissions. Security can be easily misunderstood at times, but the message text from your original post indicates the very problem you are experiencing is due to permissions:
Msg 916, Level 14, State 1, Line 4
The server principal "myuser" is not able to access the database "LinkedDB" under the current security context.
It seems like you already have a good understanding of what needs to occur, but something isn't jiving well. When I asked if you had logged into the remote server with the credentials, I was asking if you had logged into the remote server with the credentials you are passing through from the source server and tried to run the query; this guyWhat happens when you connect to the remote server in SSMS using the login and pass from this menu and run the SELECT query?
Yes, I am able to login with the remote login and everything works fine.
I do, however, have an update on this issue. I just got off a phone call and, after gaining a better understanding of what they were trying to do, I have helped come up with a solution that is going to remove the need for this TVF and this linked server.
Thanks for everyone's replies.
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply