Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

view the data from the sysmail_sentitems and sysmail_unsentitems and sysmail_faileditems without giving sysadmin role MSDB databasse Expand / Collapse
Author
Message
Posted Friday, April 12, 2013 2:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 2:53 PM
Points: 12, Visits: 121
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....
Post #1441927
Posted Friday, April 12, 2013 2:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 12,890, Visits: 31,847
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1441928
Posted Friday, April 12, 2013 3:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 2:53 PM
Points: 12, Visits: 121
Still is not working....its retrieving only view structure,not the data!!!!!
Post #1441942
Posted Sunday, April 14, 2013 12:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 2:53 PM
Points: 12, Visits: 121
waiting for best answers!!!!
Post #1442122
Posted Sunday, April 14, 2013 3:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 12,890, Visits: 31,847
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1442136
Posted Sunday, April 14, 2013 3:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 2:53 PM
Points: 12, Visits: 121
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?????
Post #1442138
Posted Sunday, May 19, 2013 10:46 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:49 AM
Points: 62, Visits: 326
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
Post #1454350
Posted Monday, May 20, 2013 7:21 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 2:18 AM
Points: 559, Visits: 1,159
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.
Post #1454522
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse