Sometime it’s necessary to allow SQL logins only to have read access to the respective SQL Servers. But it becomes a necessity to allow the developers to run the SQL Profiler when it comes to troubleshoot or to debug a flow and to find the exact point where the problem is generating. How ever it’s a very common opinion that a login having a read only access cannot run the profiler unless it’s included into the ‘sysadmin’ SQL role.
Well this isn’t entirely true. We could allow a SQL login to start and monitor SQL activity using the SQL Profiler and yet keeping the read only access to the SQL schemas. Follow these steps to achieve this:
1. Connect to the SQL Server and expand the Security->Logins node
2. Right click the desired login and go to ‘Properties’ from the context menu
3. Go to the ‘Securables’ tab and check/tick the Grant checkbox on ‘Alter Trace’. And click ok to save the details.
Now you can run the profiler and monitor details using that SQL Login.