May 15, 2015 at 9:02 am
I have a solution to the following issue but have no idea why it works....
We have a database on a 2008 R2 instances that just has sprocs and views that users can access to run reports.
Some of these use linked servers to get data.
The standard setup for sprocs is to perform an Execute As 'Login' so the users don't have direct access to the data.
We recently switched a DB from a 2008R2 instance to a 2012 instance so the report stored procedure was changed to use the new linked server that now points to a 2012 instance. The sproc started failing indicating that the table doesn't exist or user doesn't have permission.
The setup is identical as it was for the other 2008 R2 linked server. I can execute the sproc (I put myself in the linked server so i can build the sproc) BUT IT SHOULD NOT MATTER since the sproc is changing contexts right away so it executes under the ReportingAccount
I can execute it with no issues and confirmed its using the execute as account. Users however get the error. I put a line in the sproc that writes a record to an audit table. When the user would execute it doesn't write that record (its the first statement in the sproc).
What seemed to be happening is the sproc was being validated before being executed and since the user was not operating under the other context it failed to access the schema through the linked server (but worked for me since my acct was also setup in the linked server).
FIX: I put the select statement in a variable and ran it using:
Exec sp_executesql @cmd
This worked fine, I'm baffled and wondering if this has to do with hitting a 2012 linked server vs 2008 R2 linked server?
The server initiating the command is also 2008 R2
We will be doing a lot of these changes and looking for any help in explaining this. - Thanks!
May 15, 2015 at 10:08 am
In each of your linked servers, is the default login (For a login not defined...") the same and are the user mappings the same on the target server?
Before the stored procedure runs the "EXECUTE AS LOGIN", the stored procedure is using the default connection through the linked server i.e. "For Login not defined..." and if that login does not have read rights to the tables your sproc is using, the sproc thinks the db/table does not exist because it has not logged into the linked server through the "EXECUTE AS LOGIN" yet.
I would bet, the mappings of your default logins are different, the old one has read access to the table your sproc is using and the new one does not.
If you change the mapping so the default login can read the db, that would allow your old sproc to work but the security gained by "EXECUTE AS LOGIN" is lost. From a security standpoint, I think you are better off leaving the script in a variable and using "Exec sp_executesql @cmd" as your FIX states instead of granting read access to the default linked server login.
May 15, 2015 at 10:48 am
You are correct the default login was set with datareader to the old database on the prev server. Could have sworn i checked that, but that explains why it did work and also that it was setup incorrectly allowing access to tables that should not have been granted through the default.
So at least i know i wasn't going crazy :crazy:
thanks for the reply!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply