DB Trigger not running as user!

Question

DB Trigger not running as user!

jordon.shaw

Hall of Fame

Points: 3571
More actions
July 24, 2009 at 7:27 am

#156014

I have a DB trigger that when a user updates their address in a table, it sends an email. It works perfectly when I'm logged in with a dbo account; however, when a user who isn't a dbo tries to update their address, it won't allow the trigger to run and therefore, won't allow the user to update their address. Any suggestions? Is there a ways to give all users permissions to run the db trigger?
Jordon

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

jordon.shaw Hall of Fame Points: 3571 More actions · Answer 1

UPDATE: It has nothing to do with the dbo role, it has to do with the sysadmin right. If a sql user has the role of sysadmin, the trigger will run without a problem; however, if they don't have that role, it won't run and they are unable to save their address in SQL.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

From BOL:

To send Database mail, users must be a user in the msdb database and a member of the DatabaseMailUserRole database role in the msdb database. To add msdb users or groups to this role use SQL Server Management Studio or execute the following statement for the user or role that needs to send Database Mail.

Does this help?

CEWII

jordon.shaw Hall of Fame Points: 3571 More actions · Answer 3

I actually just descovered this as you posted this reply. Is there away around this?

jordon.shaw Hall of Fame Points: 3571 More actions · Answer 4

Is there a way in SQL to do like a runas? That way when any user updates their address, it would run the trigger as a user that is in the mail group? I have over 100 users on my database and don't want to have to add everyone of them to that group, or have to watch it for when a new user is created to add them to the group. Any suggestions?

Thanks,

Jordon

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

jordon.shaw (7/24/2009)
Is there a way in SQL to do like a runas?

Look up EXECUTE AS

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

I guess I don't see a need to get around this.. Grant the user access to the MSDB database to your users and add the user to the role.

I don't think run as is the answer.

Do all of your users have direct access to the database or do you have an app login?

CEWII

jordon.shaw Hall of Fame Points: 3571 More actions · Answer 7

I've tried that, but the syntax that I've seen online is:

WITH EXECUTE AS OWNER

however, when I put that in my trigger, I get a syntax error.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Look up EXECUTE AS in Books Online. There are a lot more options than just EXECUTE AS OWNER.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jordon.shaw Hall of Fame Points: 3571 More actions · Answer 9

The application is Microsoft Dynamics Great Plains; however, it uses SQL authentication for user logons, so I have around 100 accounts and really don't want to have to add every one of those to the dbmail profile.

jordon.shaw Hall of Fame Points: 3571 More actions · Answer 10

I found an easier solution than changing the code. This was an internal server, so security from the outside wasn't an issue. I just gave the public role rights to execute the sp_send_dbmail store procedure and after that, it worked perfectly! Thanks for all your help!

Jordon