locked
select from master.dbo.sysprocesses RRS feed

  • Question

  • my application is attempting to run a stored procedure that contains the following, which references the master database to get a list of users currently logged on. This will return a result when the user is in the sysadmin role. I have tried creating a role with execute permission for this store procedure and assigned to a user group. but it does not return a result. Is there a way execute the stored procedure or even the select statement using the sysadmin role?

    select distinct
    	rtrim(nt_username) as [nt_username]
    from master.dbo.sysprocesses
    where
    db_name(dbid)=DB_Name()
    and len(isnull(nt_username,''))>0
    

    Thanks

    Wednesday, March 20, 2019 2:37 AM

All replies

  • VIEW SERVER STATE permission give on the server, the user will see all executing sessions in the instance of SQL Server.

    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    • Proposed as answer by Puzzle_Chen Thursday, March 21, 2019 2:53 AM
    Wednesday, March 20, 2019 3:06 AM
  • Hi, perhaps I need to re-phrase my question.

    What is the minimum permissions that I can grant a user, so that user is able to execute a stored procedure that includes a select nt_username from master.dbo.sysprocesses?

    thanks


    Jonathan

    Wednesday, March 20, 2019 7:42 PM
  • Hi,

    If the user doesn't has the VIEW SERVER STATE permission, it will only see the current session. So you will at least grant VIEW SERVER STATE on the server and the EXECUTE permission for this store procedure.


    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, March 21, 2019 2:53 AM