Post reply

Is it possible to use SQL Server Profiler and filter by user?

  • February 1, 2010 at 1:50 am

    #216062

    Good day everyone,

    We would like to audit all sysadmin users in our server and track the changes / activities made in the database; can SQL server Profiler provide this kind of information? is it possible to filter out and only trace the activities made by the sysadmin team? and if so, how can we set up the trace?

    thanks;

  • February 1, 2010 at 7:04 am

    #1111590

    Yes, you can filter based on NTUSerName, LoginName, or SessionLoginName. Here's the differences:

    • NTUserName - is the windows user name. Is blank if a SQL login is used
    • LoginName - will show domain\username for NT logins and username for SQL logins
    • SessionLoginName - shows the original login if you use EXECUTE AS

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • February 1, 2010 at 5:14 pm

    #1112017

    Thanks so much!

    is there another way on auditing the logins aside from using the SQL profiler? we would like to know, the moment the sysadmin logged in and the queries they used; we're handling alot of servers, iam afraid that it can take too much resource if we'll use the profiler;

    Thanks again;

  • February 1, 2010 at 5:47 pm

    #1112026

    Would the default trace do what you want???

    Also, I'm curious... is this a requirement to meet a "spec" for something like SOX compliance or do you just distrust your DBAs? This isn't a sarcastic or ironic question, either. I'd really like to know for my own sake. Thanks.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 1, 2010 at 6:01 pm

    #1112033

    This is for compliance purposes; SQL profiler is ok. it provides all the queries being used, however it may take too much resource, if its tracing all the activites even if its already filtered by user; i would like too know if theres another way to check the activity w/o taking too much resource.

    thanks,

  • February 1, 2010 at 7:53 pm

    #1112051

    The default trace "may" have the information needed, but, this according a MS in a connect item, the "default trace is not for security auditing", so you probably need your own trace.

    Depending on the events you are tracing, a server-side trace, probably won't be that noticeable especially if you put the file on a fast drive away from your data, log, and backup files.

    What do you need to collect other than login/logout?

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • February 1, 2010 at 7:58 pm

    #1112052

    Thanks jack,

    i also need the activities and queries (any add, modificaion, deletion on the database) the sysadmins users executing;

  • February 1, 2010 at 8:23 pm

    #1112054

    That could potentially be a busy trace. It depends on the number of admins you have, if any applications are running under sa or a sysadmin account, and how much the admins are doing on production box. In the best case the sysadmins should be very little on a production box.

    If you run a server-side trace that traces RPC:Completed, SP:Completed, and SQL:BatchCompleted you should get everything that they are doing as those events should get everything executed on SQL Server. Of course your admins can stop the trace too!

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • February 1, 2010 at 8:30 pm

    #1112056

    thanks jack for the advice;

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply