Those who have been following this site regularly would know that a 10-part tutorial series on the SQL Server Profiler, authored by me just was recently published. You can find the master page here. This week, I would try to address some of the questions that have come from you, the kind readers as part of this series or in the “ASK” module of this site.
The question that I would address today is:
How can one filter trace events based on the Windows Login/User Name?
This is a very important requirement, especially when troubleshooting terminal-server based environments. Normally, in development environments, simply filtering on the host name is sufficient. However, in a terminal server configuration, multiple users may be logged on to the same host, which makes filtering on the windows login/username important.
Preparing the Environment
Before I execute the tests, allow me to prepare my test environment. I will be creating one stored procedure, which will be executed by 3 different windows users.
USE AdventureWorks2012 GO CREATE PROCEDURE HumanResources.proc_GetEmployes @businessEntityId INT AS BEGIN SET NOCOUNT ON SELECT SYSTEM_USER, e.BusinessEntityID, e.BirthDate, e.HireDate, e.JobTitle FROM HumanResources.Employee AS e WHERE e.BusinessEntityID = @businessEntityId END GO
As seen in the screenshot below, we have 3 Windows Users which have been granted access to SQL Server. (NOTE: In practice, it is not advisable to add users to the sysadmin fixed server role. This has been done here for demonstration purposes only):
Executing the Test
To execute the test, I have already prepared the SQL Server Profiler to use a custom template based on the “TSQL_SPs” default template as shown in the tutorial: getting-started-with-sql-server-profiler-part-2-profiler-templates-template-types-and-creating-c. As part of the customization, I have chosen to capture and display the following four columns:
Now that the SQL Server Profiler is ready, I will execute the stored procedure under the context of the 3 users referenced above. To switch the execution context, I have used the EXECUTE AS LOGIN clause.
USE AdventureWorks2012 GO --Executing as the first test user - WIN2K8SQLTestUser01 EXECUTE AS LOGIN = 'WIN2K8SQLTestUser01' EXEC HumanResources.proc_GetEmployes @businessEntityId = 1 GO --Revert to original logged-in user REVERT GO --Executing as the first test user - WIN2K8SQLTestUser02 EXECUTE AS LOGIN = 'WIN2K8SQLTestUser02' EXEC HumanResources.proc_GetEmployes @businessEntityId = 2 GO --Revert to original logged-in user REVERT GO EXECUTE AS LOGIN = 'WIN2K8Administrator' EXEC HumanResources.proc_GetEmployes @businessEntityId = 3 GO --Revert to original logged-in user REVERT GO
Looking at the Profiler, I can see that I have been able to capture the fact that the statements were executed under 3 different user contexts.
To filter the results, all one has to do is to filter one of the four columns mentioned below (as applicable):
- For both SQL & Windows Logins:
- For Windows Logins:
Trust that the above exercise has cleared the queries of all my dear readers. If you still have any queries, do send them across – I will be happy to help you out.
Until we meet next time,