Grant/Revoke Permissions to run SQL Server Profiler for a non System Admin User

Security and Permissions

Tracing can expose a lot of information about not only the state of the server, but also the data sent to and returned from the database engine by users. The ability to monitor individual queries down to the batch or even query plan level is at once both powerful and worrisome; even exposure of stored procedure input arguments can give an attacker a lot of information about the data in your database.
In order to protect SQL Trace from users that should not be able to view the data it exposes, previous versions of SQL Server allowed only administrative users (members of the sysadmin fixed server role) access to start traces. That restriction proved a bit too inflexible for many development teams, and as a result it has been loosened.



GRANT/REVOKE ALTER TRACE permission using T-SQL

 We can also assign this permission by running T-SQL commands. We can get this privilege by running the below commands in the Master database. If you want to assign this permission by running this T-SQL command then do not follow Step 3 thru Step 5 above.

--  Grant access to Windows Login
USE Master;
GO
GRANT ALTER TRACE TO [DomainName\WindowsLogin]
GO

-- To Grant access to a SQL Login
USE master;
GO
GRANT ALTER TRACE TO Geoff;
GO

GRANT permission on ALTER trace by T-SQL
Now you can go ahead and launch SQL Server Profiler to verify that you have access.

  If you want to revoke this access from the assigned login then you can run the below commands to remove the ALTER TRACE permission.

--REVOKE access FROM a SQL Login
USE Master;
GO
REVOKE ALTER TRACE FROM Geoff;
GO

-- REVOKE access FROM a Windows Login
USE master;
GO
REVOKE ALTER TRACE FROM [DomainNAME\WindowsLogin]
GO