view the data from the sysmail_sentitems and sysmail_unsentitems and sysmail_faileditems without giving sysadmin role MSDB databasse

Question

view the data from the sysmail_sentitems and sysmail_unsentitems and sysmail_faileditems without giving sysadmin role MSDB databasse

piliki

SSC-Addicted

Points: 416
More actions
April 12, 2013 at 2:29 pm

#104193

sysmail_sentitems
sysmail_unsentitems
sysmail_faileditems these are the views in msdb and without giving sysadmin role MSDB databasse,
how to access the data????
Plzz help me....

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

you'll have to make a user for each Windows group or specific login, the rest is the same you would do in any other database: create a role with only the minimal permissions to a handful of tables

USE msdb;

GO

CREATE ROLE MailReview

GRANT SELECT ON dbo.sysmail_sentitems TO MailReview;

GRANT SELECT ON dbo.sysmail_unsentitems TO MailReview;

GRANT SELECT ON dbo.sysmail_faileditems TO MailReview;

--a Windows Group login example

CREATE USER [mydomain\Developers] FOR LOGIN [mydomain\Developers];

EXEC sp_addrolemember 'MailReview','mydomain\Developers';

--a SQL login example

CREATE USER LOWELL FOR LOGIN LOWELL;

EXEC sp_addrolemember 'MailReview','LOWELL';

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

piliki SSC-Addicted Points: 416 More actions · Answer 2

Still is not working....its retrieving only view structure,not the data!!!!!

piliki SSC-Addicted Points: 416 More actions · Answer 3

piliki

SSC-Addicted

Points: 416

April 14, 2013 at 12:09 pm

#1606312

waiting for best answers!!!!

Lowell SSC Guru Points: 323504 More actions · Answer 4

piliki (4/14/2013)
waiting for best answers!!!!

the example i provided actually works; so you will have to provide the specific details of whatever you did.

a user does nto have any more rights that you give them...so you need to check what groups your user actually belongs to, because other dBA's could have assigned additional permissions, or someone got lazy and made the user a sysadmin to avoid permissions headaches.

it's trivial to test something like this as well:

--impersonate utest user - or open new window and log on as test login

execute as user='utest'

go

--check security context

print user_name()

--check your view ability to select

select * from msdb.dbo.all_mailitems

--revert to original scope/superuser

revert

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

piliki SSC-Addicted Points: 416 More actions · Answer 5

My user doesn't have any permissions,the login does have only public role on MSDB database and I don't want give sysadmin role for this particular login...,the question is with out giving sysadmin role for this particular login,user does retrieve the data from the avove view's?????

dineshvishe SSCrazy Points: 2308 More actions · Answer 6

1) Create login to user.

USE [master]

GO

CREATE LOGIN [mailuser] WITH PASSWORD=N'mailuser',

DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

2)create addmember for databseMailuser

USE [msdb]

GO

ALTER USER [mailuser] WITH DEFAULT_SCHEMA=[dbo]

GO

3)login with mailuser in SSMS

USE [msdb]

GO

EXEC sp_addrolemember N'DatabaseMailUserRole', N'mailuser'

GO

select * from msdb.dbo.sysmail_sentitems

select * from msdb.dbo.sysmail_unsentitems

select * from msdb.dbo.sysmail_faileditems

All Statement will work.

Incase any Problem,Let me know.

Regards,

Dinesh Vishe,

Dineshvishe3@gmail.com

P Jones SSChampion Points: 12352 More actions · Answer 7

sysmail_sentitems
sysmail_unsentitems
sysmail_faileditems these are the views in msdb and without giving sysadmin role MSDB databasse,
how to access the data????

Write a sql report that the user can run that executes these queries and presents the results in a friendly fashion. The account used in the datasource can have necessary role and doesn't have to be revealed to anyone and doesn't have to be the user's account.