| Author |
Topic |
|
rmg1
Constraint Violating Yak Guru
256 Posts |
 Posted - 2012-03-28 : 05:09:36
|
| Hi allI've set up a stored procedure to send emails.I can execute the stored procedure (I'm listed as a sysAdmin) but my colleagues can't (they aren't).Do you have to be a SysAdmin in order to send emails? |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2012-03-28 : 05:24:39
|
| So grant them EXEC rights to the stored procedure. |
 |
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-03-28 : 05:41:41
|
| Does that need something special in Security/Logins?I've checked the profiles are the same as mine in general except I'm a SysAdmin and they aren't. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2012-03-28 : 05:50:05
|
| Sysadmin mean that you do not need specific rights to execute the procedure, the other users do. The easiest way is to put them into a group role and then assign the exec rights to that role.Look up GRANT EXEC in BoL. |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-03-28 : 06:33:09
|
| We're trying to keep the sending of automated mails down a select few people (preferably without the use of roles, etc).I've tried to use the GRANT statement but without success.The syntax I used was:-grant execute on sp_send_dbmail to usernamebut I got an error.The username is in the format of firstname.lastname, does this cause any other issues? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-03-28 : 07:49:19
|
| I did, and I'm using that to pass parameters to the msdb.dbo.sp_send_dbmail stored procedure.I'm just getting a general "ODBC Call Failed" error (the first execute statement is being created on the fly in Access VBA which in turn calls msdb.dbo.sp_send_dbmail).The ODBC details are correct as they are used for other items in the same database. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2012-03-29 : 03:58:59
|
| sp_send_dbmail is in msdb, are you sure you are calling it correctly and whichever access user you are using has the rights to run it?Try logging on to the SQL server and try running the procedure as one of the users who currently can't seem to run it. You will get a better error message for troubleshooting the problem. |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-03-29 : 04:00:14
|
| I've been trying to add a new group to try and sort this out but I'm struggling.I've checked the help files and it looks like you can't add a user-defined server role on SQL 2008.I really don't want to have to go through add specific permissions to specific people (there's a few and it would be a nightmare to keep them up to date).Is there any other way round this? |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-03-29 : 04:06:32
|
The error I get is a basic execute permission denied.The code for the my stored prcoedure is:- exec msdb.dbo.sp_send_dbmail @profile_name='CIT' ,@recipients = @recipient_list ,@subject = @subject_line ,@body = @body_text @recipient_list, @subject_line and @body_text are passed as paramaters |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2012-03-29 : 04:08:39
|
| Did you have a look at the link? Did you make sure your users had DatabaseMailUserRole in msdb?I have never had these kind of issues with database mail, it just works, so will be something in your security model. |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-03-29 : 04:35:14
|
| They've only got a guest acount on MSDB with a public profile.I had a look at the link, but I didn't want to grant special permissions to specific people. I was hoping to go down the group/role you mentioned earlier.I can't add a new Server Role (the option isn't available in SQL 2008) so I was hoping there was another way of grouping people together and then giving the group the special permission needed.It would be far easier to administer for new starters, leavers, etc. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2012-03-29 : 04:50:25
|
| OK, can you create a new windows security group? This may be the easiest way around this. Get a group created and then add the users to that windows group. This way you can then just add that windows group into SQL and you never have to worry about rights again once you have that groups rights correct. |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-03-29 : 05:25:26
|
| Don't think I have permissions to create a new Windows security group, everything is locked down on local machines and servers.Can user-defined groups be added to SQL in any other way? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2012-03-29 : 06:05:02
|
| Not as far as I know. Ask your admin to set up a new group and which members to add to it and sure they'll do it, it is a 5 minute job. |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-03-29 : 06:37:03
|
| OK, thanks. I'll have a word with our IT bods and see what they say. |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-03-29 : 07:45:44
|
| I've done a bit more googling and it appears there's a CREATE ROLE statement, and it appears to exist in SQL Server 2008 R2 which we are using.Would that be any use? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2012-03-29 : 08:12:40
|
| Yes, that would work, does mean you would need to administer it for new starters and leavers though, whereas doing it through windows groups would just make it an IT admin task. |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-03-29 : 08:52:02
|
| I can live with that and it gives us more control over who does what.It looks like it's a database level role and not server level though. Don't know if that makes a difference.Would I create this on the database that has the original stored procedure or on the MSDB database that holds the sp_send_dbmail stored procedure? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2012-03-29 : 11:02:00
|
| You'd need to have the role in msdb to be able to run sp_send_dbmail. They will also need permissions on your database to exec the sp, so this way you will need to administer in two places. |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-03-30 : 02:09:19
|
| That's fine, I can live with that.Thanks for all the help. |
|
|
|
Next Page
|
SQL Server 2008 emails