Post reply

SQL 2019 - Grant a regular user (domain account) read/see user accounts and...

  • June 8, 2022 at 3:35 pm

    #4046307

    Is it possible to grant a regular user (domain account) read/see user accounts and SQL agent jobs?

    We are doing an upgrade and the application admin wants to see user accounts, linked servers and sql agent jobs (job properties also?) and compare from old SQL server to the new SQL server.

    Short of granting sysadmin role, I am not aware of any other way. ¯\_(ツ)_/¯

    Any help is greatly appreciated.

     

    Thanks

    • This topic was modified 2 years, 3 months ago by  Warren Peace.

  • June 8, 2022 at 4:20 pm

    #4046325

    Create a stored procedure WITH EXECUTE AS OWNER.

    (being sure to secure the procedure so only the intended people can execute it)

  • June 8, 2022 at 5:29 pm

    #4046384

    Or run the stored procedure yourself and email them the results in a spreadsheet.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • June 8, 2022 at 5:38 pm

    #4046433

    For each database do ( including msdb - will allow view jobs )

    GRANT VIEW DEFINITION TO [<USERNAME>]

    And

    USE [msdb]

    GO

    ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [<username>]

    GO

    USE [msdb]

    GO

    ALTER ROLE [SQLAgentUserRole] ADD MEMBER [<u8sername>]

    GO

    • This reply was modified 2 years, 3 months ago by  jonau1.
    • This reply was modified 2 years, 3 months ago by  jonau1.

  • June 16, 2022 at 8:54 pm

    #4050628

    Thanks, I will give this a try this week.

  • June 16, 2022 at 11:49 pm

    #4050687

    jonau1 wrote:

    For each database do ( including msdb - will allow view jobs )

    GRANT VIEW DEFINITION TO [<USERNAME>]

    And

    USE [msdb] GO ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [<username>] GO USE [msdb] GO ALTER ROLE [SQLAgentUserRole] ADD MEMBER [<u8sername>] GO

     

    if decide to do the above then read this https://qa.sqlservercentral.com/forums/topic/sqlagentreaderrole-can-create-its-own-jobs-what-is-ms-thinking - you don't really need any user creating jobs on your server (even if they won't be able to do much with those jobs)

Viewing 6 posts - 1 through 5 (of 5 total)

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