question

narendba avatar image
narendba asked

how to set profiler on perticular login?

We have to set profiler for perticular login.... When anything running by that login i need to capture the details of that perticular login... how I can set the condition for it.... Can anyone give me the step by step process on it?
sql-server-2005
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

narendba avatar image narendba commented ·
Thanks powel it is just simply great one... But I want other thing is, When that perticular login will login to server and do something only that trace have to be run and get the details... So How can i do that?
0 Likes 0 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
Updated the answer with link to SQL Traces.
0 Likes 0 ·
narendba avatar image narendba commented ·
it that link i wouldn't get any process to automate it :( Can you guide me with other way?
0 Likes 0 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
What you would like to automate? Once the Trace has been set-up with appropriate filters, it will collect all the data selected automatically. You define events you want to collect and filters (collect data for particular login) and SQL Server will write all that information to a trace file once you enable that trace. If you will read though the MSDN Site, you will find right way. See also [How to: Create a Trace (Transact-SQL)][1] where is a small example [1]: http://msdn.microsoft.com/en-us/library/ms188662(v=sql.105).aspx
0 Likes 0 ·
narendba avatar image narendba commented ·
Pavel, The trace start and stop when the login logged in and did something... after he loggedout the trace stopped... Again when user logged in onceagain the trace not start... So when the perticular login logged into server that time trace file also start and take the details.. when he loggedout the trace stop the work automatically and start when that user loggedin once again...? So how i can automate the above process?
0 Likes 0 ·
Show more comments

1 Answer

·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
This is simple. When you are setting up a Trace Properties in the Profiler, switch to Events Selectuion tab and ensure, that you have a LoginName column selected. Once you have that column selected, you can click on the Column Filters button and in next dialog select the LoginName column and write the Like expression to contain the login name, which you want to monitor. After that when the Trace will be running, Profiler will be collecting information only for that particular login. **EDIT:** In case the traces should be run automatically, you can define SQL Straces. For details related to SQL Traces take a look on MSDN: [Introducing SQL Trace][1]. You can use a [LOGON triggers (MSDN)][2] to start the trace once the user Logs in for the first time. You will check whether the trace exists and if not, then you start the trace. Lter you do not need to take care about the trace much as it will collect event only for that particuar user (of course if you setup the trace filters properly). If you want to shut down the trace automatically, than you can implement an Event Notification quee and processing for the Audit Logout events. Some sample you can find for example in this blog post [Auditing User Log Out Events][3] by JimMcLeod, as there is no LOGOUT trigger. Of course it will require adaptation to your needs. [1]: http://msdn.microsoft.com/en-us/library/ms191006(v=sql.105) [2]: http://msdn.microsoft.com/en-us/library/bb326598.aspx [3]: http://www.jimmcleod.net/blog/index.php/2008/05/28/auditing-user-log-out-events/
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
+1. But I would tend to pick SessionLoginName instead of LoginName. SessionLoginName would filter on the Original Login. But I guess LoginName by pass ExecuteAs different user
1 Like 1 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
Yeah right @Usman. Anyway bettwr would be to collect both as also somebody else can Execute AS given user. :-) But dont' know how to define "OR" operator among filter columns.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.