I trust that all you are finding my tutorial series on the SQL Server Profiler useful. Recently, I received an interesting question related to assigning permissions to a SQL Server user to allow them to use the SQL Server Profiler.

Today, using a simple experiment, I will be demonstrating the following:

1. What minimal permissions are required for a user to capture SQL traces using the SQL Server profiler?
   
2. How to assign these minimum permissions to a SQL Server user?

The Demo

To begin with establishing an environment for the experiment, I will first create a SQL Server login and a user associated to that login.

–Creating the logins/users

USE master

GO

CREATE LOGIN ProfilerTest01

WITH PASSWORD = ‘Passwd@12’, CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF, DEFAULT_DATABASE = AdventureWorks2008R2

GO

USE AdventureWorks2008R2

GO

CREATE USER ProfilerTest01

FOR LOGIN ProfilerTest01

GO

Normal logins/users cannot run the Profiler

I will now launch the SQL Server Profiler and attempt to login using this newly created login/user – ProfilerTest01. The error message that I receive is:

Looking at the message again indicates that in order to allow this user to use the SQL Server Profiler, I have a choice in either providing the user sysadmin fixed server role access (not advised) or grant the ALTER TRACE permission.

In order to run a trace against SQL Server you must be a member of sysadmin fixed server role or have the ALTER TRACE permission.

Not Recommended: Providing sysadmin fixed server role access is not recommended because it would grant the login/user complete control over the entire SQL Server instance and therefore does not go with the “minimum permissions” paradigm.

The only choice therefore is to provide the user ALTER TRACE permissions.

Solution – Assign the ALTER TRACE permissions

The solution therefore is to assign ALTER TRACE permissions to the login using the following GRANT statement. The ALTER TRACE permission is a server-scoped permission being granted to a server-level principal (a login) which means that granting somebody this permission will expose the entire SQL Server instance for the mentioned purpose (in this case, tracing).

–Granting permissions to the logins/users

USE master

GO

GRANT ALTER TRACE TO ProfilerTest01

GO

It is important here to switch back to the master database because the permission that we are attempting to grant is a server scoped permission and therefore not being in the master database can result in the following error:

Msg 4621, Level 16, State 10, Line 1

Permissions at the server scope can only be granted when the current database is master

Now that I have executed the script to GRANT the ALTER TRACE permission, I will now attempt to use the SQL Server Profiler with this login. I can see that the login succeeded and the Profiler brought the trace properties window.

After configuring the trace (Part 02-Customizing Profiler Templates), I execute the following test statements below while the Profile trace is running. The test statements are random queries for demonstration only.

–Test Workload (Login to the Profiler with the test login before executing the workload)

USE AdventureWorks2008R2

GO

SELECT * FROM HumanResources.Employee

GO

USE master

GO

SELECT * FROM sys.configurations

GO
USE msdb

GO

SELECT * FROM sys.systypes

GO
USE AdventureWorksLT

GO

SELECT * FROM BuildVersion

GO

SECURITY WARNING! The key point to note here is that I am executing queries against multiple databases in the system. The login running the trace does not have permissions to the “AdventureWorksLT” database, yet queries executed against that database can be traced by the user in the Profiler. This is because the ALTER TRACE is a server-scoped permission as mentioned above.

SQL Logins v/s Windows Logins

While the experiment above was demonstrated using SQL Logins, the process remains the same if SQL logins mapped to a Windows logins are to be used. I trust that these steps will help you grant Profiler permissions to your development team without having to make them sysadmins on your SQL Server instances.

References:

• Profiler Permissions : http://msdn.microsoft.com/en-us/library/ms187611.aspx
  
• GRANT statement : http://msdn.microsoft.com/en-us/library/ms186717.aspx

Until we meet next time,

Be courteous. Drive responsibly.