#0200-SQL Server-Permissions for SQL Server Profiler-Required access for a user/login


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:


The SQL Server Profiler can only be used by a member of sysadmin fixed server role or have the ALTER TRACE permissions


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


SQL Server Profiler showing captured traces from all databases due to the ALTER TRACE server-scoped permission


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:



Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

8 thoughts on “#0200-SQL Server-Permissions for SQL Server Profiler-Required access for a user/login

  1. Nakul Vachhrajani

    Thank-you, one and all for your wishes. I am glad that you find my posts useful. With your support, I hope to continue writing many more in the future!

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s