To find the user who shut down the SQL Server

• Venkatesan Prabu

  SSCommitted

  Points: 1892

  More actions

  April 14, 2010 at 12:22 am

  #233879

  Hi All,

  I am having a very urgent issue. Someone have shut down the SQL Server database and I need to find out who have done it.

  1. Checked windows logs.

  2. Checked user logs.

  I am getting the message SQL Server service is stopped and user is N/A.

  I couldn't find who done it. From the logged in status from windows log, I can identify the person but I can't pinpoint that, he have done it. Do we have any possibilities to identify who shut down the SQL server.

  Cheers,

  Venkatesan Prabu .J

  Thanks and Regards,
  Venkatesan Prabu,
  My Blog:

  http://venkattechnicalblog.blogspot.com/

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  April 14, 2010 at 11:34 am

  #1151589

  First - remember that members of sysadmin and serveradmin fixed servers have shutdown privileges then check who are the members.

  Second - check if anybody has the specific privilege granted, you can use query below...

  SELECT

  [srvprin].[name] [server_principal],

  [srvprin].[type_desc] [principal_type],

  [srvperm].[permission_name],

  [srvperm].[state_desc]

  FROM [sys].[server_permissions] srvperm

  INNER JOIN [sys].[server_principals] srvprin

  ON [srvperm].[grantee_principal_id] = [srvprin].[principal_id]

  WHERE [srvprin].[type] IN ('S', 'U', 'G')

  AND [srvperm].[permission_name] = 'SHUTDOWN'

  ORDER BY [server_principal], [permission_name];

  Third - once you get your list of suspects check who had access to the system at the time system went down; now you have your target suspects.

  At this point in time round up the suspects to a dark dungeon and apply third degree interrogation techniques as needed

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.