November 1, 2005 at 10:34 am
I have created a SQL authenticated login and added this login to public in msdb (in order to view the DTS packages), and added the login to a db role (role has heightened priveleges in the db). This is an emergency id...developers request the login be activated in order to do debugging on DTS packages.
The problem is when the developer connects to the server using the sql emerid and attempts to execute a step in the package, they receive an error that they do not have permission to execute a sp. The emerid DOES have permission to execute the sp...so it's like the dts is using the developer's nt login (which does not have access to execute the sp) instead of the emerid.
Anyone else found this problem and if so...how did you fix/workaround it?
thanks!
November 2, 2005 at 7:33 am
Is the SP executed through a SQL task? Do you know what SP is trying to be executed? I've never looked into this but does DTS uses SP's within the msdb to write package logs etc. These, I assume these would be executed as the user that is running the package?
November 2, 2005 at 7:38 am
Yep, the proc is executed in a sql task. What I don't get is if the developer is connecting to sql via a sql id, why does msdb not see that...it's like it's seeing the NT id. I confirmed this by giving the nt explicit permission to execute the proc and then the developer was able to execute it.
November 2, 2005 at 7:52 am
Is the developer is updating all of the necessary connections within the package to use the new emerid user account or are they just using the emerid user to logon to SQL to be able to edit the package?
November 2, 2005 at 3:48 pm
Daniel, thanks so much. I must've had a brainfart...it didn't occur to me to change the db connection object. Once we did that, it worked.
thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply