locked
SQL Server Security RRS feed

  • Question

  • Hello All,

    I have a situation here, our windows domain admin password is shared between couple of people. Which included me, my boss and DBA. Now, the issues is that our DBA just messes up things and then leave it to me to sort out. I am the only IT admin in the company. The very person runs CPU intensive queries on SQL Server 2008 in day time and clogs up the database for other people. Is there any kind of monitoring I can setup so that all the queries he run on a machine are logged somewhere? or any other solution or pointer will be appreciated.

    Any help will be highly appreciated.

    Thanks

    Wednesday, July 4, 2012 10:01 AM

Answers

  • I have a situation here, our windows domain admin password is shared between couple of people. Which included me, my boss and DBA. Now, the issues is that our DBA just messes up things and then leave it to me to sort out.

    The best one thing I can say convey to lead to give indivual Id's to perform the operation instead of shared one so that it will be an indivual roles comes in to picture and security will be tight.

    Secondaly you have to set for the SQL server autdit for the successful and failure audit on logons then if you want to track the queries to run by the user then you

    can use SQL proflier to track but you cannot keep run the proflier all the time instead if you feel that during an particular time or for analyzing purpose run the profiler and capture the log then anyalze and notify to the users.

    use security  audit option.

    Download the below E-book it has some very useful information to track only CPU intesive queries and other etc....

    http://www.sqlservercentral.com/blogs/aloha_dba/2009/01/21/mastering-sql-server-profiler-e-book-available-for-free/

    about proflier
    http://msdn.microsoft.com/en-us/library/ms181091.aspx


    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    • Marked as answer by Maggie Luo Wednesday, July 18, 2012 2:44 PM
    Wednesday, July 4, 2012 9:27 PM
    • For all set of queries
    • You can setup a server side profiler trace to capture queries
    • You can have auditing comes with SQL server 2008 version

    For instant look :-

    -- Top 3 CPU-sapping queries for which plans exist in the cache       
    SELECT TOP 3
            total_worker_time ,
            execution_count ,
            total_worker_time / execution_count AS [Avg CPU Time] ,
            CASE WHEN deqs.statement_start_offset = 0
                      AND deqs.statement_end_offset = -1
                 THEN '-- see objectText column--'
                 ELSE '-- query --' + CHAR(13) + CHAR(10)
                      + SUBSTRING(execText.text, deqs.statement_start_offset / 2,
                                  ( ( CASE WHEN deqs.statement_end_offset = -1
                                           THEN DATALENGTH(execText.text)
                                           ELSE deqs.statement_end_offset
                                      END ) - deqs.statement_start_offset ) / 2)
            END AS queryText
    FROM    sys.dm_exec_query_stats deqs
            CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
    ORDER BY deqs.total_worker_time DESC ;

    Please click the Mark as Answer or Vote As Helpful  if a post solves your problem or is helpful!



    • Edited by RohitGarg Thursday, July 5, 2012 10:34 AM
    • Marked as answer by Maggie Luo Wednesday, July 18, 2012 2:44 PM
    Thursday, July 5, 2012 10:32 AM

All replies

  • You can set SQL server auditing,

    Please have look on below link

    http://msdn.microsoft.com/en-us/library/cc280386.aspx

    http://msdn.microsoft.com/en-us/library/dd392015%28v=sql.100%29.aspx

    Wednesday, July 4, 2012 12:48 PM
  • I have a situation here, our windows domain admin password is shared between couple of people. Which included me, my boss and DBA. Now, the issues is that our DBA just messes up things and then leave it to me to sort out.

    The best one thing I can say convey to lead to give indivual Id's to perform the operation instead of shared one so that it will be an indivual roles comes in to picture and security will be tight.

    Secondaly you have to set for the SQL server autdit for the successful and failure audit on logons then if you want to track the queries to run by the user then you

    can use SQL proflier to track but you cannot keep run the proflier all the time instead if you feel that during an particular time or for analyzing purpose run the profiler and capture the log then anyalze and notify to the users.

    use security  audit option.

    Download the below E-book it has some very useful information to track only CPU intesive queries and other etc....

    http://www.sqlservercentral.com/blogs/aloha_dba/2009/01/21/mastering-sql-server-profiler-e-book-available-for-free/

    about proflier
    http://msdn.microsoft.com/en-us/library/ms181091.aspx


    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    • Marked as answer by Maggie Luo Wednesday, July 18, 2012 2:44 PM
    Wednesday, July 4, 2012 9:27 PM
    • For all set of queries
    • You can setup a server side profiler trace to capture queries
    • You can have auditing comes with SQL server 2008 version

    For instant look :-

    -- Top 3 CPU-sapping queries for which plans exist in the cache       
    SELECT TOP 3
            total_worker_time ,
            execution_count ,
            total_worker_time / execution_count AS [Avg CPU Time] ,
            CASE WHEN deqs.statement_start_offset = 0
                      AND deqs.statement_end_offset = -1
                 THEN '-- see objectText column--'
                 ELSE '-- query --' + CHAR(13) + CHAR(10)
                      + SUBSTRING(execText.text, deqs.statement_start_offset / 2,
                                  ( ( CASE WHEN deqs.statement_end_offset = -1
                                           THEN DATALENGTH(execText.text)
                                           ELSE deqs.statement_end_offset
                                      END ) - deqs.statement_start_offset ) / 2)
            END AS queryText
    FROM    sys.dm_exec_query_stats deqs
            CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
    ORDER BY deqs.total_worker_time DESC ;

    Please click the Mark as Answer or Vote As Helpful  if a post solves your problem or is helpful!



    • Edited by RohitGarg Thursday, July 5, 2012 10:34 AM
    • Marked as answer by Maggie Luo Wednesday, July 18, 2012 2:44 PM
    Thursday, July 5, 2012 10:32 AM