March 28, 2012 at 11:57 pm
We have a specific security requirement whereby a linked server is permissioned using login mapping. If a user is not in the list of mappings, they can't use the linked server (For a login not defined ... connections will Not be made). However, we have a group of administrators who perform the code deployments that utilise these linked servers. They have administrative access to the system which hosts the stored procedure but not to the target of the linked servers.
When trying to deploy code, thy receive the error:
Msg 7416, Level 16, State 1, Procedure myProc, Line 11
Access to the remote server is denied because no login-mapping exists.
Is there a way to create the stored procedure such that it doesn't follow linked servers for schema validation similar to deferred name resolution? Something like a NOCOMPILE option whereby this step is performed by the first executor of the code. I'm pretty certain the response will be "No, that's a first execution timebomb and SQL protects you from that" but surely that would be no different to genuine deferred name resolution where the absence of a reference table or stored procedure is accepted with only a warning message? Our current resolution is to temporarily add a login mapping, create the procedure and remove the mapping but that's a pain and requires the involvement of additional colleagues.
Thanks in anticipation.
March 29, 2012 at 8:27 am
Pete Bishop (3/28/2012)
We have a specific security requirement whereby a linked server is permissioned using login mapping. If a user is not in the list of mappings, they can't use the linked server (For a login not defined ... connections will Not be made). However, we have a group of administrators who perform the code deployments that utilise these linked servers. They have administrative access to the system which hosts the stored procedure but not to the target of the linked servers.When trying to deploy code, thy receive the error:
Msg 7416, Level 16, State 1, Procedure myProc, Line 11
Access to the remote server is denied because no login-mapping exists.
Is there a way to create the stored procedure such that it doesn't follow linked servers for schema validation similar to deferred name resolution? Something like a NOCOMPILE option whereby this step is performed by the first executor of the code. I'm pretty certain the response will be "No, that's a first execution timebomb and SQL protects you from that" but surely that would be no different to genuine deferred name resolution where the absence of a reference table or stored procedure is accepted with only a warning message? Our current resolution is to temporarily add a login mapping, create the procedure and remove the mapping but that's a pain and requires the involvement of additional colleagues.
Thanks in anticipation.
I do not think you can. Erland has summed it up better than I could, and even provided a neat workaround to try...hope it helps.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 29, 2012 at 9:14 am
The execution context will be whateve the SQLagent account it. So, you could create the agent job logged in as the SQL agent account and make sure the SQL Agent account is one of the mapped linked server logins.
Another option is to set up a credential based login to run this job, allow it to be SQLAgent operator role and use run as.
The probability of survival is inversely proportional to the angle of arrival.
March 29, 2012 at 6:07 pm
Thanks for the link. Annoying but as expected 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply