April 3, 2009 at 9:28 am
This *was* working and now it isn't. I've checked everything, I can login as the 'EXECUTE AS' user and do everything. This user has all the necessary permissions to do anything it needs. Still, when broker attempts to activate the procedure it fails with the 'Login failed for user *****' in the SQL Server Log.
Anybody have any ideas?
The probability of survival is inversely proportional to the angle of arrival.
April 3, 2009 at 1:44 pm
sturner (4/3/2009)
This *was* working and now it isn't. I've checked everything, I can login as the 'EXECUTE AS' user and do everything. This user has all the necessary permissions to do anything it needs. Still, when broker attempts to activate the procedure it fails with the 'Login failed for user *****' in the SQL Server Log.Anybody have any ideas?
Would be able to post the more exact error please?
April 3, 2009 at 1:56 pm
The exact error in the SQL Server log is:
The activated proc [dbo].[QSVThreatAssesmentProcessor] running on queue [Errors].[dbo[.[QSVInputQueue] output the following: 'Login failed for user 'xxxxxx'.'
The procedure is being activated and executed but evidently reports an error somewhere... The user 'xxxxxx' is the EXECUTE AS user for the activation procedure.
When I log in to SQL Server as that user and execute the activation procedure from a session it executes perfectly and does what it is supposed to do. The frustrating thing about this is that it was working and appears to have stopped when I made the database 'trustworthy' . Maybe I should set trustworthy off and try it again, but we will be bouncing the server tomorrow morning so I'd like to see if that helps.
The probability of survival is inversely proportional to the angle of arrival.
April 3, 2009 at 2:06 pm
What permissions does the user have and why did you put TRUSTWORTHY ON, any specific reason behind that?
From BOL:
Because a database that is attached to an instance of SQL Server cannot be immediately trusted, the database is not allowed to access resources beyond the scope of the database until the database is explicitly marked trustworthy. Also, modules that are designed to access resources outside the database, and assemblies with either the EXTERNAL_ACCESS and UNSAFE permission setting, have additional requirements in order to run successfully.
April 3, 2009 at 2:16 pm
The user has the necessary permissions as a login and a user in that database to execute procedures and update some tables. This has all been verified by logging in as that user and testing.
As I stated, this *was* working before. I did change the database owner as well but I changed it back to the original owner when this problem cropped up. The database needs to be trustworthy in preparation for some enhancements and features we will be adding in the future.
The probability of survival is inversely proportional to the angle of arrival.
April 4, 2009 at 5:55 am
PROBLEM RESOLVED.
What was causing this was an addition of a linked server insert... a single row insert to a table on another server, to the activated stored procedure.
Even though no inserts were actually attempted (this was just a log entry insert in case of a failure) the mere presence of this linked server insert caused the activation procedure to fail to run with the resulting error message. When I commented the insert out and recompiled the procedure everything works as expected.
So this experience tells me two things: execution of a stored procedure in a 'EXECUTE AS' context is not the same as that user actually logged in and executing the procedure... there are some things that will not work, even though the database is set to 'TRUSTWORTHY' and that user normally would have appropriate permissions.
BE careful out there!!
The probability of survival is inversely proportional to the angle of arrival.
April 4, 2009 at 6:22 am
Glad it's resolved even though I wasn't able to help you much. thanks for sharing with us.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply