Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
 Posted - 2008-10-23 : 10:24:54
|
| I have a requirement, where I need to be notified when a new DB is created on any server. I have created a DDL trigger, which uses sp_send_dbmail to send the mail notification. Sometimes when a non-sysadm login (but a member of dbcreator serverrole) tries to create a DB, I get the exception – inadequate permissions to use sp_send_dbmail. The solution is to make these logins members of DatabaseMailUserRole msdb role. Or probably give public access to msdb mail sps (which I don’t want to do)I tried to map DBcreator server role with DatabaseMailUserRole and found it is not possible. There are many of servers and logins with dbcreator access gets created frequently. Is there a better solution?------------------------I think, therefore I am - Rene Descartes |
|
|
cvraghu
Posting Yak Master
187 Posts |
Posted - 2008-10-24 : 03:55:27
|
| DDL and Notification are two different process. Rights for the former should not affect the later. The second process (Trigger/whatever) should use an account that has these rights. |
 |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-10-24 : 09:23:00
|
| The piece that did the trick is "WITH EXECUTE AS SELF" on the trigger definition.------------------------I think, therefore I am - Rene Descartes |
|
|
|
|
|
|
DDL trigger and mail