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

  • 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 months, 1 week ago by  Warren Peace.
  • Create a stored procedure WITH EXECUTE AS OWNER.

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

  • 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

  • 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 months, 1 week ago by  jonau1.
    • This reply was modified 2 months, 1 week ago by  jonau1.
  • Thanks, I will give this a try this week.

  • 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://www.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)

  • The commands below worked per database...

    GRANT VIEW DEFINITION TO [<USERNAME>]

    I was misinformed, they didn't want to see the agent jobs, they want to see what accounts exist in the SQL server for comparison.

    Is there a way to allow a regular user (sql or domain) to see/list accounts in SSMS?

  • What do you mean by account ?   login or  user  database or both ?

    If the question is only for login there here are 2 possibilities that I know :

    1)

    ALTER SERVER ROLE [securityadmin] ADD MEMBER [<loginname>]

    This is way  much more than just view,  <loignname>  will be able to modify security on that server

    2)

    For each login that you want the application user to see do

    GRANT VIEW DEFINITION ON LOGIN::[<loginnameN> ] TO [<loginname>]

    loginnameN :   each login name that you allow the  application user to see ( if there are 100 than you have to do a 100 grant )

    loginname  :  login that the application user use to login

    By default  each login can only see sa and its own login

     

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

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