Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

We are running SQL SERVER 2008, I have a webpage that is executing a stored procedure. This stored procedures runs various updates to a several tables which have triggers. The error I am receiving when the web site runs is below

Error 6549, Level 16, State , Procedure trg_TAA_DFLRemoval, Line1, message: A .NET framework error occurred during execution of user defined routine or aggregate 'trg_tAA_DFLRemoval':

which is followed by all the method calls.

I have tried

GRANT EXECUTE ON ASSEMBLY::[T2AuditTrigger] TO [NT AUTHORITY\NETWORK SERVICE]

But there seems to be a syntax error in my sql generated script. While searching I found that Microsoft decided not to allow GRANT EXECUTE to function. Is this an accurate statement in regards to my problem.

What I cant seem to find out is how to get the user permission to run the Assembly.

share|improve this question
    
sqlteam.com/forums/topic.asp?TOPIC_ID=101984 according to the last post the work around is to grant the user execute permissions to the entire database, this seems not to work –  george9170 Aug 5 '09 at 19:34

2 Answers 2

First, you'll want to grant the rights to the account that the web server is logging into the SQL Server as. Hopefully that isn't the NETWORK SERVICE account.

That error "A .NET framework error occurred during execution of user defined routine or aggregate 'trg_tAA_DFLRemoval':" is an error from the .NET framework, not from the SQL Server, so something is blowing up within the .NET method you are calling. What happens when you call the method manually from T/SQL?

share|improve this answer
    
The NETWORK SERVICE account is default for IIS. Why would running under it be bad? I can run the procedure manually from t/sql. The reason why i say it is on the tsql side is, the stored procedure executes 80% of its code, it blows up when it hits the table with the trigger. I have verified this through sql profiler. –  george9170 Aug 5 '09 at 12:59
    
msdn.microsoft.com/en-us/library/ms998320.aspx This states that the Network Service account is safe to run under, and should be used for IIS access to sql server. Can you point me to a site which states otherwise. –  george9170 Aug 5 '09 at 13:02
    
Thank you for the help, this is my first time posting and dont want to sound like abutthole. –  george9170 Aug 5 '09 at 13:42
up vote 0 down vote accepted

It was a sql permissioning error, we have an auditing system turned on. For some reason the user running the query triggered the auditing system. THis auditing system ran a trigger that the user didnt have rights to. I had to grant this user rights to the trigger.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.