Home Forums SQL Server 7,2000 Administration view the data from the sysmail_sentitems and sysmail_unsentitems and sysmail_faileditems without giving sysadmin role MSDB databasse RE: view the data from the sysmail_sentitems and sysmail_unsentitems and sysmail_faileditems without giving sysadmin role MSDB databasse

  • 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!