July 13, 2017 at 2:44 am
Hi There,
Can you please let me know how do I provide select permission on sysmail_allitems table under MSDB database to a non-sysadmin user?
I need to provide select permission on this table so that user can view "all" the emails and their status, if there is any mails which were not sent/ failed, he can take the appropriate actions.
BOL says it requires sysadmin permission which I do not wish to provide to the user.
Following is the link to BOL
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sysmail-allitems-transact-sql
July 13, 2017 at 10:49 pm
Thanks Thom,
However it didnt worked. That user is not able to see the records.
July 14, 2017 at 1:44 am
What do you mean it "didn't work"? We'll need more information than that.What was the error? What were they trying to do?
What was the SQl you ran in total. A quick check with a test login and user works on my 2012, 2016, and 2017 CTP instances, so I would expect a step is missing.USE master;
GO
--Create test login
CREATE LOGIN EmailChecker WITH PASSWORD='23908!%"%ts8h1tha238gna82';
GO
Use msdb;
GO
--Create User
CREATE USER EmailChecker FOR LOGIN EmailChecker;
GO
--Grant access
GRANT SELECT ON sysmail_allitems TO EmailChecker;
GO
--Check it works
EXECUTE AS LOGIN = 'EmailChecker';
GO
SELECT SYSTEM_USER AS [Login], CURRENT_USER AS [User];
SELECT top 100 *
FROM sysmail_allitems;
GO
--Revert permissions
REVERT;
GO
--Cleanup
DROP USER EmailChecker;
USE master
DROP LOGIN EmailChecker;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 19, 2019 at 10:31 am
I realize this thread is 2 years old, but I figured I'd finish the item and describe the work around instead of leaving the thread dangling since it shows up so high on Google searches.
The problem is the sysmail_allitems view requires someone to be in the fixed system role SYSADMIN to see all the records, otherwise they will only see the mail they tried to send:
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sysmail-allitems-transact-sql?view=sql-server-2017
a workaround is to use sysmail_mailitems instead, which you can easily grant SELECT permissions on if you just want an operator type person to be able to investigate the queued and sent mail history.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply