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, 7 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)

  • 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, 7 months ago by  jonau1.
    • This reply was modified 2 years, 7 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://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)

  • July 13, 2022 at 2:39 pm

    #4062799

    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?

  • July 13, 2022 at 9:00 pm

    #4062966

    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 7 (of 7 total)

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