What permissions to be provided when a user wants to monitor who is accessing the server.

SSG 1 Reputation point
2021-12-02T05:49:12.233+00:00

Hi All,

We have a request where users wants to monitor who are all accessing the database server.
What sql permission can be granted to the user other than sysadmin?

Thank You.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,814 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-12-02T07:28:14.31+00:00

    Hi @SSG ,

    You can configure login auditing in SQL Server to monitor SQL Server Database Engine login activity. The audit result is written to SQL server error log.

    You can configure login audit as below screenshot. You must restart SQL Server before this option will take effect.
    154354-screenshot-2021-12-02-152709.jpg
    154355-screenshot-2021-12-02-152223.jpg

    154332-screenshot-2021-12-02-152437.jpg

    154346-screenshot-2021-12-02-153753.jpg
    Refer to MS document Configure Login Auditing (SQL Server Management Studio) to get more.

    You need to have the EXECUTE permission on the object 'xp_instance_regwrite', database 'mssqlsystemresource', schema 'sys'. Suggest you using sysadmin account. I create a user1 login, the login is in public role, using user1 account to configure the SQL server login audit, I got below error message.
    154364-screenshot-2021-12-02-155050.jpg
    We change property of SQL server to configure SQL login audit. Quote from MS document View or Change Server Properties,

    To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

    Suggest you using sysadmin account to configure SQL server login audit.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments
  2. Ronen Ariely 15,096 Reputation points
    2021-12-02T23:35:13.487+00:00

    hi,

    I agree with Erland.

    Without more information my first approach will be to NOT give any permission to users except to simple user tables. Store the auditing in simple user tables, and let your users have simple SELECT permission on these tables.

    If you must and only if you must which you should not come to this requirement, then let your users to use specific monitoring tools/queries. In this case, we need to know exactly which queries/tool they must be able to execute, and you should give them the minimum permission for these tasks. Give us the information on what they need to execute and we can discuss the minimum privileges

    But again, this is not recommended to give simple users such permissions. In most cases I would recommend to store the information in simple tables (or files) for your users

    0 comments