#0193-SQL Server-Profiler-Filtering trace events based on Windows Login/User Name


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):

  1. WIN2K8Administrator
  2. WIN2K8SQLTestUser01
  3. WIN2K8SQLTestUser02

image

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:

  • LoginName
  • LoginSid
  • NTDomainName
  • NTUserName

image

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.

image

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:
    • LoginName
    • LoginSid
  • For Windows Logins:
    • NTDomainName
    • NTUserName

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.

References:

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

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